SQL SELECT multiple conditions from many to many relationships

Question

SQL SELECT multiple conditions from many to many relationships

I am struggling to find an elegant solution to this problem. I have 5 tables and their relationship is described in the image. enter image description here

A page can contain multiple products, and each product can have many ProductRates. A page with a specific product can have a lot of bids. To work around many of the problems, there is a PageToProductToRate table.

Users want to request multiple conditions, where the choice can be a combination of any of them:

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 clause and expected results: enter image description here

And further: enter image description here

The query that works for me is using INTERSECT to get correct results. I tried UNION but didn't get all the results.

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 to solve this problem? I could have over a dozen of these conditions and a query with all the joins could get out of hand.

+3

sql sql-server sql-server-2008

AidaM June 12. 15 at 19:28

source to share

3 answers

My best guess.

WITH products AS 
(
    SELECT  [Product],
            [ProductRatesID]
    FROM    Product p
            JOIN ProductRates pr ON p.[Product] = pr.[Product]
    WHERE   (p.[Product] = 'ALMOND' AND pr.CommissionType = 'PREMIUM' AND pr.Rate = 0.25)
            OR (p.[Product] = 'WALNUT' AND pr.CommissionType = 'SERVICE FEE' AND pr.Rate = 1.25)
            OR (p.[Product] = 'HAZELNUT' AND pr.CommissionType = 'EXCESS' AND pr.Rate = 1.68)
)
SELECT  P.[PageID]
FROM    [Page] P
        JOIN (
            SELECT  p2p.[PageID], COUNT(*) as ProductCount
            FROM    products pr
                    JOIN PageToProduct p2p ON p2p.[Product] = pr.[Product]
                    JOIN PageToProductToRate p2p2r on p2p2r.[PageToProductID] = p2p.[PageToProductID] 
            WHERE   p2p2r.[ProductRatesID] = pr.[ProductRatesID]
            GROUP BY p2p.[PageID]
        ) sq ON sq.[PageID] = p.[PageID]
WHERE   sq.ProductCount = @ProductFilterCount

You need to figure out how you want to process @ProductFilterCount

. This can be either the number of number filters you use, or the number of products that actually match those filters.

SQL Fiddle

+1

JamieD77 June 12. 15 at 20:13

source to share

I'm not sure what you are asking .. but if you want pages that have product choices then this might work, you have to test yourself though

edit- changed to AND because he wants all conditions to be met

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) 
  AND (PR.[Product] = 'WALNUT' AND R.CommissionType = 'SERVICE FEE' AND R.Rate = 1.25)
  AND (PR.[Product] = 'HAZELNUT' AND R.CommissionType = 'EXCESS' AND R.Rate = 1.68)

0

tshoemake June 12. 15 at 19:46

source to share

shawnt00 · Accepted Answer · 2015-06-13T01:59:32+0000

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 */

SQL SELECT multiple conditions from many to many relationships

More articles: