TSQL, select values from large many-to-many relationship

Question

I have two tables Publishers and Campaigns, both have similar many-to-many relationships with Countries,Regions,Languages and Categories.

more info

Publisher2Categories has publisherID and categoryID which are foreign keys to publisherID in Publishers and categoryID in Categories which are identity columns. On other side i have Campaigns2Categories with campaignID and categoryID columns which are foreign keys to campaignID in Campaigns and categoryID in Categories which again are identities. Same goes for Regions, Languages and Countries relationships

How do I get campaignIDs of Campaigns that have at least one equal to Publisher (I pass to query publisherID) value from regions, countries, language or categories?

click here for direct link

thanks

Answer 1

JOINING each individual manytomany table from publishers and campaigns and UNIONthe result should leave you with all campaignid's that have a match between publishers and campains through one of there respective relation tables.

SELECT  cc.CampaignID
FROM    dbo.PublisherSites2Countries pc
        INNER JOIN dbo.Campaings2Countries cc ON cc.CountryID = pc.CountryID
UNION SELECT pr.CampaignID
FROM    dbo.PublisherSites2Regions pr
        INNER JOIN dbo.Campaings2Regions cr ON cr.RegionID = pr.RegionID
...

Answer 2

You probably want to find linked Campaigns and Publishers one join-type at a time, and union the results:

SELECT DISTINCT campaignID
  FROM Campaigns2Countries c2cy
 WHERE EXISTS (SELECT * FROM PublisherSites2Countries
                WHERE countryID = c2cy.countryID
                  AND publisherID = :publisherID)
UNION
SELECT DISTINCT campaignID
  FROM Campaigns2Categories c2cat
 WHERE EXISTS (SELECT * FROM PublisherSites2Categories
                WHERE categoryID = c2cat.categoryID
                  AND publisherID = :publisherID)
UNION
...