StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>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 ;)</p> <p>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:</p> <pre><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; </code></pre>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload