w3hello.com logo
Home PHP C# C++ Android Java Javascript Python IOS SQL HTML videos Categories
How do I determine if a group of data exists in a table, given the data that should appear in the group's rows?

This is a set-within-sets query. I like to solve it using group by and having:

select groupid
from GroupValues gv
group by groupid
having sum(case when value = 'a' then 1 else 0 end) > 0 and
       sum(case when value = 'b' then 1 else 0 end) > 0 and
       sum(case when value = 'c' then 1 else 0 end) > 0 and
       sum(case when value not in ('a', 'b', 'c') then 1 else - end) = 0;

The first three conditions in the having clause check that each elements exists. The last condition checks that there are no other values. This method is quite flexible, for various exclusions and inclusion conditions on the values you are looking for.

EDIT:

If you want to pass in a list, you can use:

with thelist as (
      select 'a' as value union all
      select 'b' union all
      select 'c'
     )
select groupid
from GroupValues gv left outer join
     thelist
     on gv.value = thelist.value
group by groupid
having count(distinct gv.value) = (select count(*) from thelist) and
       count(distinct (case when gv.value = thelist.value then gv.value
end)) = count(distinct gv.value);

Here the having clause counts the number of matching values and makes sure that this is the same size as the list.

EDIT: query compile failed because missing the table alias. updated with right table alias.





© Copyright 2018 w3hello.com Publishing Limited. All rights reserved.