Hi team,
Have a spec table with master table and its child table product information
Objective is to get the product with L-pipe and elongated shape.
Itemid :1234
Declare @testspecName table (specId int,SpecName varchar(50))
Insert Into @testspecName
Values (101, 'Type')
, (102, 'Shape')
select * from @testspecName
Declare @testspecVal table (specValueId int,specId int,specValue varchar(50))
Insert Into @testspecVal
Values (201, 101,'L-pipe,Z-pipe')
,(202, 102,'round,elongated')
select * from @testspecVal
Declare @testspecItem table (specItemId int,specId int,specValue varchar(50),specitem int)
Insert Into @testspecItem
Values (301,102,'elongated', 1234)
,(302, 101,'L-pipe',1234)
,(303, 102,'round',5678)
,(304, 101,'L-pipe',5678)
select * from @testspecItem
This is my tried query, but it returns both items
select ts.specitem from @testspecItem ts
inner join @testspecVal tv on ts.specId = tv.specId
where ts.specValue in ('elongated','L-pipe')
group by ts.specitem
Any support is appreciated