Hello,

I'm having a problem with the following pretty large and complicated (for me
at least ;-)) query:

SELECT Campaign.ID, Campaign.name,
CampaignCategory.ID AS categoryID,
CampaignCategory.name AS categoryName,
(MAX(CampaignProductCommission.impressionCommissio nFixed) * 1000) AS
cpmCommissionFixed,
MAX(CampaignProductCommission.clickCommissionFixed ) AS
clickCommissionFixed,
MAX(CampaignProductCommission.leadCommissionFixed) AS
leadCommissionFixed,
MAX(CampaignProductCommission.saleCommissionFixed) AS
saleCommissionFixed,
MAX(CampaignProductCommission.saleCommissionVariab le) AS
saleCommissionVariable,
CASE WHEN
CampaignSegment_Affiliate.campaignSegment_Affiliat eStatusID >= 2 THEN
(
CampaignSegment_Affiliate.campaignSegment.ID
)
ELSE
(
SELECT CampaignSegment.ID
FROM CampaignSegment
WHERE CampaignSegment.isGeneral AND
CampaignSegment.campaignID = Campaign.ID
)
END AS "selectedSegmentID"
FROM Campaign
LEFT JOIN CampaignCategory ON (CampaignCategory.ID =
Campaign.campaignCategoryID)
LEFT JOIN CampaignSegment ON (CampaignSegment.campaignID = Campaign.ID)
LEFT JOIN CampaignSegment_Affiliate ON
(CampaignSegment_Affiliate.campaignSegmentID = "selectedSegmentID" AND
CampaignSegment_Affiliate.affiliateID = '" . $this->user->ID . "')
LEFT JOIN CampaignSegment_CampaignProduct ON
(CampaignSegment_CampaignProduct.campaignSegmentID = "selectedSegmentID")
LEFT JOIN CampaignProduct ON (CampaignProduct.ID
IN (
SELECT campaignProductID
FROM CampaignSegment_Affiliate
WHERE campaignSegmentID = "selectedSegmentID"
))
LEFT JOIN CampaignProductCommmission ON
(CampaignProductCommission.campaignProductID = CampaignProduct.ID)

WHERE Campaign.campaignStatusID = '2'
GROUP BY Campaign.ID, Campaign.name, Campaign.date, CampaignCategory.ID,
CampaignCategory.name
ORDER BY Campaign.date DESC, Campaign.ID DESC
LIMIT 5 OFFSET 0

So what it should do is this:

Select campaign ID, campaign name, campaign category ID, campaign category
name and the maximum cpm/click/lead and sale commission for the products in
the segment the affiliate belongs to.

It is possible that the affiliate doesn't belong to a segment yet
(CampaignSegment_Affiliate table). In that case it should use the "general"
segment, identified by the "isGeneral" field in the "CampaignSegment" table.

The problem with this query is that I cannot use "selectedSegmentID" (in the
CASE) in the LEFT JOIN beneath. Is there any other way I can do this in one
PGSQL query? Or is there anyone with a hint for me ;-)? I already searched
google and postgresql's documentation, but didn't find a way to do it yet.

Thanks a bunch in advance! I really appreciate your time .

Kind regards,

Tim



---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend