Query to get rows containing exact column values for Group in SQL Server

Last Reply one month ago By abhi619

Posted one month ago

I have a sql table where I want to get GroupName having exact Variants in the table

GroupName VariantID
GRP724_1_4 1
GRP724_1_4 2
GRP724_1_4 3
GRP724_1_4 4
GRP724_5_2 2
GRP724_5_2 3
GRP724_7_3 1

like if i search for group name having variant id 2,3 then it should show only GRP724_5_2

I want to search group name who have exact those variants like "GRP724_5_2" have exact "2,3" but "GRP724_1_4" have "1,2,3" in it.

So if I search for Variant ID 1 then result should be "GRP724_7_3" because that group have only one variant.

Posted one month ago

Got the query

select GroupName
from VariantGroups as t
group by GroupName
having sum(case when VariantID = 1 then 1 else 0 end) > 0 and
       sum(case when VariantID = 2 then 1 else 0 end) > 0 and
       sum(case when VariantID = 3 then 1 else 0 end) > 0 and
       sum(case when VariantID not in (1,2, 3) then 1 else 0 end) = 0;