SQL Query: Selecting a person who is a member of all existing groups I'm tinkering around with my very first SQL queries and am completely baffled by a problem I encountered.

Let's say I have the following database:

PERSON(PID, Name)
MEMBER(PID, GroupID)


How would I go about creating an SQL query that selects only the tuples of the persons who are a member of all existing groups (i.e. GroupIDs)?

I tripped over everything I could find regarding IN, EXISTS, ALL and the likes without finding an elegant solution.

Thank you very much,

Pety
There are several ways to get to this answer.  I'll tackle it in two parts.  The first is to find out how many groups there are.  This is a simple summary query:

select count(distinct GroupID)
from MEMBER


The second is how many groups one person is in.  This is similar:

select
  PID,
  COUNT(*)
from MEMBER
group by PID


Next you want to tie these together

select
  PID,
  COUNT(*)
from MEMBER
group by PID
having COUNT(*) = (select count(distinct GroupID)
                   from MEMBER)


Depending on which RDBMS you're using (Oracle, DB2, MySQL) there may be other syntaxes or layouts which make this faster, prettier or otherwise more desirable.