Clever Geek Handbook

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


source to share


3 answers 


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


+1


source


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


source


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


source






More articles:


All Articles