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

Solution

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);