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 1 |
+----+---------+
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 | Bike 1 | 1 (MTB) |
| 2 | Bike 1 | 5 (Blue) |
| 3 | Bike 1 | 7 (26 inch) |
| 4 | Bike 2 | 2 (Tourer) |
| 5 | Bike 2 | 4 (Red) |
| 6 | Bike 2 | 7 (26 inch) |
| 7 | Bike 3 | 3 (Racer) |
| 9 | Bike 3 | 2 (Black) |
| 10 | Bike 3 | 8 (29 inch) |
| 11 | Bike 4 | 1 (MTB) |
| 12 | Bike 4 | 2 (Black) |
| 13 | Bike 4 | 7 (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