SQLTeam.com | Weblogs | Forums

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

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