CODEWITHSUNDEEP
sqlsql-server
Eyal
Eyal

Reputation: 4763

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
758     25     96     0
758     97     954    1
758     194    2902   1
758     194    2903   1

The result should be:

Id  IsExists
27     1

Is it possible?

Upvotes: 1

Views: 984

Answers (3)

objectNotFound
objectNotFound

Reputation: 1783

Try this ... it uses the inner group by to get the distinct counts of IsExists by ID and PID and the outer one checks if there are 2 or more

SELECT ID, 1 as IsExists FROM
(
    select ID, PID ,  Count(Distinct IsExists) as IsExists 
    FROM
    (

        Select 27 as ID , 2 as PID , 1 as IsExists UNION ALL
        Select 27 as ID , 2 as PID , 0 as IsExists UNION ALL
        Select 27 as ID , 5 as PID , 1 as IsExists UNION ALL
        Select 51 as ID , 9 as PID , 1 as IsExists UNION ALL
        Select 51 as ID , 9 as PID , 0 as IsExists UNION ALL
        Select 51 as ID , 10 as PID , 0 as IsExists

    ) a
    WHERE IsExists = 1
    Group by ID, PID
) B
GROUP BY B.ID 
Having  Count(*) >= 2

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269463

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(distinct case when isexists = 1 then pid end);

Upvotes: 2

dnoeth
dnoeth

Reputation: 60462

You need a nested aggregation:

select Id
from
 (
   select Id, Pid, 
      -- returns 1 when value exists
      max(IsExists) as maxExists
   from tab
   group by Id, Pid
 ) as dt
group by Id
     -- check if all Pid got a 1
having min(maxExists) = 1

Upvotes: 1

Related Questions