Need help with select - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Need help with select

Author

Topic

fengfeng
Yak Posting Veteran

64 Posts

Posted - 2011-05-11 : 16:20:07

I have the following query:

 

SELECT SUM(vz.sales), vz.campaign_id, camp.reallocate_reason, camp.parent_campaign_id FROM vz_sales vz,vz_campaigns camp
WHERE vz.campaign_id = camp.id  AND  campaign_id = 12345

This query shows the campaign id, the sale of the campaign id, why the campaign was reallocated, and the original campaign that it originated from. So for campaign 12345, that was originally created from the parent campaign id of 23456 due to reallocated reason. How can I show the parent campaign id's sales as well?

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-05-11 : 20:57:05

Would this do it for you?


WITH CTE AS
(
   SELECT
      SUM(vz.sales) CampaignSales,
      vz.campaign_id,
      camp.reallocate_reason,
      camp.parent_campaign_id
   FROM
      vz_sales vz,
      vz_campaigns camp
   WHERE
      vz.campaign_id = camp.id
      AND campaign_id = 12345
)
SELECT 
   a.CampaignSales,
   a.campaign_id,
   a.reallocate_reason,
   a.parent_campaign_id,
   b.CampaignSales AS ParentSales
FROM
   CTE a
   LEFT JOIN CTE b ON a.parent_campaign_id = b.campaign_id

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources