Hi,
Trying to build matching filters for product category against size and color. Have master tables for category,size and color and product options table.
Declare @categoryMaster table (id int,catName varchar(50))
Insert Into @categoryMaster
Values (1, 'Caps')
, (2, 'Shirts')
select * from @categoryMaster
Declare @sizeMaster table (id int,sizeName varchar(50))
Insert Into @sizeMaster
Values (101, 'Small')
, (102, 'Medium')
, (103, 'Large')
, (104, 'FullSleeve')
, (105, 'HalfSleeve')
select * from @sizeMaster
Declare @colorMaster table (id int,colorName varchar(50))
Insert Into @colorMaster
Values (201, 'Red')
, (202, 'Green')
, (203, 'Blue')
, (204, 'White')
select * from @colorMaster
-- prod options
Declare @productOptions table (prodid int,catid int,size int,colorid int)
Insert Into @productOptions
Values
(5000,1,101,201)
,(5001,1,101,202)
,(5002,1,102,201)
,(5003,1,103,202)
,(6000,2,104,203)
,(6001,2,105,204)
select * from @productOptions
---Expected Result--