kombsh kombsh - 2 years ago 86
SQL Question

How to write SQL query for following scenario

I have a table in following structure.

Group Member/Owner Type

G1 M1 Member
G1 OW1 Owner
G2 OW1 Owner
G3 OW1 Owner
G3 OW2 Owner
G4 M2 Member
G4 OW2 Owner

Now, I want to query all Groups that has only Owner and do not have a single member.

The required query should return the groups G2 and G3 for above table since it has only owners and not a single member.

Can anyone help me to achieve this?

Answer Source

You can do this with aggregation and having:

select [group]
from t
group by [group]
having min(type) = 'Owner' and max(type) = 'Owner';

This says that the minimum and maximum value of type is 'Owner' -- which means that the value is always 'Owner' (or possibly NULL).

Alternatively, you can express the having clause as:

having sum(case when type = 'Member' then 1 else 0 end) = 0
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download