Fetch record based on single column

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

can you try this

Select ts.specitem from @testspecItem ts
inner join @testspecVal tv
on ts.specId = tv.specId
where ts.specValue='elongated' and
ts.specitem in(Select ts.specitem from @testspecItem ts
inner join @testspecVal tv
on ts.specId = tv.specId
where ts.specValue='L-pipe')

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
having max(case when ts.specValue = 'elongated' then 1 else 0 end) = 1 and
    max(case when ts.specValue = 'L-pipe' then 1 else 0 end) = 1
order by ts.specitem
1 Like

Thank you for the response. If the criteria of values ie.'elongated,round' etc are dynamic based on user selection, will it be possible to use above query?

This approach , to me imho, is worrisome

L-pipe,Z-pipe

Comma delimited values in one column.

1 Like