CODEWITHSUNDEEP
sqlsql-serverfiltermany-to-many
MRB
MRB

Reputation: 65

Filtering Products by multiple (a set of) Many to Many Variations

Can anyone please help me to filter a products table, by a set of Many to Many Variations in SQL Server? Filtering by a single variation is straightforward, but I can't get my head around multiple.

I have setup a SQL fiddle here: https://sqlfiddle.com/sql-server/online-compiler?id=489fb6f3-1b8e-4256-88be-11a4c900e900

PRODUCTS

Id Name
1 Bike 1
2 Bike 2

Variations

Id Name
1 Style
2 Colour
3 Wheel Size

VariationValues

Id VariationId ValueName
1 1 MTB
2 1 Tourer
3 1 Racer
4 2 Red
5 2 Blue
6 2 Black
7 3 26 inch
8 3 29 inch

ProductVariations

Id ProductId VariationValueId
1 1 (Bike 1) 1 (Style = MTB)
2 1 (Bike 1) 5 (Colour = Blue)
3 1 (Bike 1) 7 (Wheel Size = 26 inch)
4 2 (Bike 2) 2 (Style= Tourer)
5 2 (Bike 2) 4 (Colour = Red)
6 2 (Bike 2) 7 (Wheel Size = 26 inch)
7 3 (Bike 3) 3 (Style = Racer)
9 3 (Bike 3) 2 (Colour = Black)
10 3 (Bike 3) 8 (Wheel Size = 29 inch)
11 4 (Bike 4) 1 (Style = MTB)
12 4 (Bike 4) 2 (Colour = Black)
13 4 (Bike 4) 7 (Wheel Size = 26 inch) 
-- This query gets the bikes that match this style
DECLARE @Style int = 1; -- MTB (should find Bike 1 and Bike 4, which it does)

SELECT p.Name, vv.ValueName --,vv.Id AS ValueId, pv.VariationValueId
FROM Products p
INNER JOIN ProductVariations pv ON pv.ProductId = p.Id
INNER JOIN VariationValues vv ON vv.Id = pv.VariationValueId
WHERE pv.VariationValueId = @Style
ORDER BY p.Name

-- But the problem is, how to select products that match  multiple variations?

 -- This will NOT work
DECLARE @Style int = 1; -- MTB
DECLARE @Colour int = 5; -- BLUE
DECLARE @WheelSize int =7; -- 26 inch

SELECT p.Name, vv.ValueName --,vv.Id AS ValueId, pv.VariationValueId
FROM Products p
INNER JOIN ProductVariations pv ON pv.ProductId = p.Id
INNER JOIN VariationValues vv ON vv.Id = pv.VariationValueId
WHERE pv.VariationValueId = @Style
AND pv.VariationValueId = @Colour
AND pv.VariationValueId = @WheelSize
ORDER BY p.Name

Thanks for any help

Upvotes: 2

Views: 38

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522506

Aggregation is one canonical way to approach this. Assuming you just want matching product names alone, you may use:

SELECT p.Name
FROM Products p
INNER JOIN ProductVariations pv
    ON pv.ProductId = p.Id
INNER JOIN VariationValues vv
    ON vv.Id = pv.VariationValueId
WHERE pv.VariationValueId IN (@Style, @Colour, @WheelSize)
GROUP BY p.Name
HAVING COUNT(DISTINCT pv.VariationValueId) = 3
ORDER BY p.Name;

The idea here is to aggregate by product name, restricting to only records having the three variation values you want, then assert that all 3 variations are present for each matching product.

If you want to also select other columns, then place the above into a CTE, and filter based on matching products only:

WITH cte AS (
    SELECT p.Name
    FROM Products p
    INNER JOIN ProductVariations pv ON pv.ProductId = p.Id
    INNER JOIN VariationValues vv ON vv.Id = pv.VariationValueId
    WHERE pv.VariationValueId IN (@Style, @Colour, @WheelSize)
    GROUP BY p.Name
    HAVING COUNT(DISTINCT pv.VariationValueId) = 3
)

SELECT p.Name, vv.ValueName, vv.Id AS ValueId, pv.VariationValueId
FROM Products p
INNER JOIN ProductVariations pv
    ON pv.ProductId = p.Id
INNER JOIN VariationValues vv
    ON vv.Id = pv.VariationValueId
WHERE p.Name IN (SELECT Name FROM cte);

Upvotes: 2

Related Questions