CODEWITHSUNDEEP
sqlsql-server
Abhi619
Abhi619

Reputation: 47

Select Group name by its content in SQL query

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     |
    +-----------+-----------+
    |GRP724_7_3 |     2     |
    +-----------+-----------+
    |GRP724_7_3 |     3     |
    +-----------+-----------+

I want to search group name who have exact those variants like if I search for VariantID "2","3" then result should be "GRP724_5_2" because it have exact "2,3". So if I search for Variant ID "1,2,3" then result should be "GRP724_7_3".

Upvotes: 0

Views: 905

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270401

Use group by and having:

select groupname
from t
group by groupname
having 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 (2, 3) then 1 else 0 end) = 0;

If you want to pass the value in as a string, then use string_split() or a similar function to convert the string to a table. Assuming no duplicates in the string or the original table:

with v as (
      select convert(int, value) as variantid
      from string_split(@s) s
     )
select t.groupname
from t left join
     v
     on t.variantid = v.variantid
group by t.groupname
having count(*) = (select count(*) from v) and
       count(*) = count(variantid);

You can handle duplicates with judicious use of count(distinct).

Upvotes: 1

Related Questions