CODEWITHSUNDEEP
sqlsql-servert-sql
user460114
user460114

Reputation: 1848

SQL Server - Query help

I have the following tables:

MEDIA

MediaID     MediaTypeID   Title
---------------------------------------------
100         25            Photo of Anton Oliver
200         25            Photo of Hika Eliot

CAMPAIGNS (campaigns use media and belong to pools)

CampaignID   MediaID   PoolID
----------------------------
1            100       1
2            100       2
3            200       1

CAMPAIGN POOLS

PoolID       PoolName
--------------------
1            Pool1
2            Pool2
3            Pool3

Given a specific campaignID and poolID passed to the query, I want the following select query to determine whether the media belongs to the passed campaign AND/OR Pool

SELECT m.* 
FROM media m
<!--- whatever required joins --->

So, the resulting recordset would look something like this:

MediaData

MediaID   MediaTypeID   Title  BelongsToCampaign   BelongsToPool
----------------------------------------------------------------
1         25             xyz       0                   1

I hope that makes sense.

Upvotes: 1

Views: 89

Answers (3)

onedaywhen
onedaywhen

Reputation: 57023

I'm not sure from your spec you want the resultset to return all media or only certain subsets (as per @BizApps's answer) -- posting more than two rows of sample data might help here ;)

The following unions the four possible subsets: remove or comment out the ones you don't need. I trust you can see where CTEs could be used to reduce repetition in code:

SELECT m.*, t.BelongsToCampaign, t.BelongsToPool
  FROM (
        -- Campaign INTERSECT Pool
        SELECT c.MediaID, 1 AS BelongsToCampaign, 1 AS BelongsToPool
          FROM CAMPAIGNS AS c
         WHERE c.CampaignID = @CampaignID
        INTERSECT 
        SELECT MediaID, 1, 1
          FROM CAMPAIGN_POOLS AS p JOIN CAMPAIGNS AS c ON p.PoolID = c.PoolID
         WHERE p.PoolID = @PoolID

        -- Campaign EXCEPT Pool
        UNION
        SELECT c.MediaID, 1, 0
          FROM CAMPAIGNS AS c
         WHERE c.CampaignID = @CampaignID
        EXCEPT 
        SELECT MediaID, 1, 0
          FROM CAMPAIGN_POOLS AS p JOIN CAMPAIGNS AS c ON p.PoolID = c.PoolID
         WHERE p.PoolID = @PoolID

        -- Pool EXCEPT Campaign
        UNION
        SELECT MediaID, 0, 1
          FROM CAMPAIGN_POOLS AS p JOIN CAMPAIGNS AS c ON p.PoolID = c.PoolID
         WHERE p.PoolID = @PoolID
        EXCEPT 
        SELECT c.MediaID, 0, 1
          FROM CAMPAIGNS AS c
         WHERE c.CampaignID = @CampaignID

        -- Media in neither Pool nor Campaign
        UNION
        SELECT m.MediaID, 0, 0
          FROM MEDIA AS m
        EXCEPT 
        SELECT c.MediaID, 0, 1
          FROM CAMPAIGNS AS c
         WHERE c.CampaignID = @CampaignID
        EXCEPT 
        SELECT MediaID, 1, 0
          FROM CAMPAIGN_POOLS AS p JOIN CAMPAIGNS AS c ON p.PoolID = c.PoolID             
         WHERE p.PoolID = @PoolID
       ) AS t 
       JOIN MEDIA AS m ON m.MediaID = t.MediaID;

Upvotes: 0

user710502
user710502

Reputation: 11471

select m.MediaID, m.MediaTypeID, m.mediaTitle as Title, c.CampaignID as BelongsToCampaign,     cp.PoolID as belongsToPool
from campaings c, media m,  campaign_pools cp
where (m.mediaid = c.mediaid) and (c.poolId = cp.poolId) and 
(c.campaignID = [your variable] or cp.PoolId = [your other variable])

Upvotes: 0

BizApps
BizApps

Reputation: 6130

You can do something like this:

Select MediaID,MediaTypeID,Title,ISNULL(c.CampaignID,0) as  BelongsToCampaign,ISNULL(p.PoolID,0) as  BelongsToPool
From Media m
LEFT OUTER JOIN Campaign c on m.MediaID=c.MediaID
LEFT OUTER JOIN Pools p on p.PoolID=c.PoolID
where c.CampaignID=@CampaignID OR p.PoolID=@PoolID

Regards

Upvotes: 3

Related Questions