SQL SELECT multiple conditions many to many relationship

Question

I am struggling to find an elegant solution to this problem. I have 5 tables and their relationships are described in the image.

A page can have multiple products and each product can have many ProductRates. A Page with specific Product could have many rates as well. To get around the many to many issue there is table PageToProductToRate.

Users want to query on multiple conditions where the selection could be combination of any:

• Product1 + Rate1 + rate attribute1
• Product1 + Rate1 + rate attribute2
• Product1 + Rate2 + rate attribute2
• Product2 + Rate3 + rate attribute1 etc...

This is an example of data and WHERE condition and expected results:

And another:

The query that works for me uses INTERSECT to get the right results. I tried UNION but would get results not matching all of the conditions.

SELECT DISTINCT P.[PageID]
FROM [Page] P
  join PageToProduct p2p on p2p.[PageID] = P.[PageID] 
  join Product pr on p2p.[Product] = pr.[Product] 
  join PageToProductToRate p2p2r on p2p2r.[PageToProductID] = p2p.[PageToProductID] 
  join ProductRates r on r.[ProductRatesID] = p2p2r.[ProductRatesID] 
  WHERE (PR.[Product] = 'ALMOND' AND R.CommissionType = 'PREMIUM' AND R.Rate = 0.25) 
INTERSECT
SELECT DISTINCT P.[PageID]
FROM [Page] P
  join PageToProduct p2p on p2p.[PageID] = P.[PageID] 
  join Product pr on p2p.[Product] = pr.[Product] 
  join PageToProductToRate p2p2r on p2p2r.[PageToProductID] = p2p.[PageToProductID] 
  join ProductRates r on r.[ProductRatesID] = p2p2r.[ProductRatesID] 
  WHERE (PR.[Product] = 'WALNUT' AND R.CommissionType = 'SERVICE FEE' AND R.Rate = 1.25)
INTERSECT
SELECT DISTINCT P.[PageID]
FROM [Page] P
  join PageToProduct p2p on p2p.[PageID] = P.[PageID] 
  join Product pr on p2p.[Product] = pr.[Product] 
  join PageToProductToRate p2p2r on p2p2r.[PageToProductID] = p2p.[PageToProductID] 
  join ProductRates r on r.[ProductRatesID] = p2p2r.[ProductRatesID] 
  WHERE (PR.[Product] = 'HAZELNUT' AND R.CommissionType = 'EXCESS' AND R.Rate = 1.68)

Is there a better way of going on around this problem? I could potentially have over a dozen of these conditions and the query with all the joins could get out of the control.

Answer 1

SELECT p2p.PageID
FROM
    PageToProduct as p2p
    inner join Product as pr
        on p2p.Product = pr.Product
    inner join PageToProductToRate as p2p2r
        on p2p2r.PageToProductID = p2p.PageToProductID
    inner join ProductRates as r
        on r.ProductRatesID = p2p2r.ProductRatesID 
WHERE
          (pr.Product = 'ALMOND' AND r.CommissionType = 'PREMIUM' AND r.Rate = 0.25) 
      OR  (pr.Product = 'WALNUT' AND r.CommissionType = 'SERVICE FEE' AND r.Rate = 1.25)
      OR  (pr.Product = 'HAZELNUT' AND r.CommissionType = 'EXCESS' AND r.Rate = 1.68)
GROUP BY p2p.PageID
HAVING COUNT(*) = 3; /* requires all three are present, as long as no rows are duplicate */