Eyal Eyal - 4 months ago 9
SQL Question

SQL select & Group if at least one exists

I need to select & Group

Id
&
Pid
when at least 1 for each
Pid
in
Id
has
IsExists=1


Id Pid Opt IsExists
27 2 107 1
27 2 108 0
27 5 96 1
51 9 17 1
51 9 18 0
51 10 112 0


The result should be:

Id IsExists
27 1



  • In the result for
    [id=27 | pid=2]
    & for
    [id=27 | pid=5]
    has at least 1 with
    isExists=1



Is it possible?

Answer

One method uses two levels of aggregation:

select id
from (select id, pid, max(isexists) as max_isexists
      from t
      group by id, pid
     ) t
having count(*) = sum(max_isexists);

This assumes that isexists takes on the values 0 and 1.

An alternative only uses one level of aggregation but is a bit trickier, using count(distinct):

select id
from t
group by id
having count(distinct pid) = count(case when isexists = 1 then pid end);