Filtering Products by multiple Many to Many Variations

You must execute the code again to save those values in the database.

-- Products
CREATE TABLE Products (
  ProductId INT IDENTITY(1, 1),
  ProductName VARCHAR(100)
);
SET IDENTITY_INSERT [Products] ON ;
INSERT INTO Products([ProductId], [ProductName]) VALUES (1, 'Bike 1');
INSERT INTO Products([ProductId], [ProductName]) VALUES (2, 'Bike 2');
INSERT INTO Products([ProductId], [ProductName]) VALUES (3, 'Bike 3');
SET IDENTITY_INSERT [Products] OFF ;

-- Variations
CREATE TABLE Variations (
  VariationId INT IDENTITY(1, 1),
  VariationName VARCHAR(100)
);
SET IDENTITY_INSERT [Variations] ON ;
INSERT INTO Variations([VariationId], [VariationName]) VALUES 
(1, 'Style'),
(2, 'Colour'),
(3, 'Wheel Size');
SET IDENTITY_INSERT [Variations] OFF ;

-- VariationValues
CREATE TABLE VariationValues (
  VariationValueId INT IDENTITY(1, 1),
  VariationId int,
  VariationName VARCHAR(50)
);

INSERT INTO VariationValues([VariationId], [VariationName]) VALUES 
(1, 'MTB'),
(1, 'Tourer'),
(2, 'Red'),
(2, 'Blue'),
(2, 'Black'),
(3, '26 inch'),
(3, '29 inch');

-- ProductVariations
CREATE TABLE ProductVariations (
  ProductVariationId INT IDENTITY(1, 1),
  VariationValueId INT,
  ProductId INT
);
INSERT INTO ProductVariations([VariationValueId], [ProductId]) VALUES 
(1, 1), -- Bike 1, MTB
(1, 4), -- Bike 1, Blue
(1, 6), -- Bike 1, 26 inch
(2, 2), -- Bike 2, Tourer
(2, 3), -- Bike 2, Red
(2, 6); -- Bike 2, 26 inch

-- Get the bike that matches these varations
DECLARE @Style int = 1; -- MTB
DECLARE @Colour int = 4; -- BLUE
DECLARE @WheelSize int =6; -- 26 inch
SELECT p.ProductName, vv.VariationName FROM Products p --p.ProductId, p.ProductName
inner join ProductVariations pv on pv.ProductId = p.ProductId
inner join VariationValues vv on vv.VariationValueId = pv.VariationValueId
order by p.ProductName
/*
inner join ProductsToVariationValues pv on pv.ProductId = p.Id
inner join VariationValues vv on vv.Id = pv.variationValueId
inner join Variations vars on vars.Id = vv.VariationId
*/

ProductName	VariationName
Bike 1	MTB
Bike 2	Tourer
Bike 3	Tourer

(1 rows affected)

(1 rows affected)

(1 rows affected)

(3 rows affected)

(7 rows affected)

(6 rows affected)

(3 rows affected)