SQL: Find the names of the people in at least the same clubs as one specific person

Question

I have this table: club(clubname, membername)

photography | Jim
photography | Eve
photography | Alex
woodworking | Jim
woodworking | Alex
cooking     | Alex

How do I find the names of people who are in at least the same clubs as Jim?

In this example, I want to return Alex.

I know how to find the names of people in ANY of the same clubs as Jim

SELECT DISTINCT C1.membername
FROM clubname C1, clubname C2
WHERE C1.clubname = C2.clubname AND C2.membername = 'Jim" AND C1.membername <> 'Jim'

But how do I specify that I only want the people who are in ALL of the same clubs as Jim?

See [Divided We Stand: The SQL of Relational Division](https://www.simple-talk.com/sql/t-sql-programming/divided-we-stand-the-sql-of-relational-division/) as suggested in [this](http://stackoverflow.com/a/4759138/2055998) answer to a relared question. — PM 77-1, Mar 22 '15 at 05:41

score 1 · Answer 1 · answered Mar 22 '15 at 05:59

I was looking for a solution similar to a set difference operator which is basically the link PM 77-1 posted.

This is what I got in the end:

SELECT membername
FROM club
WHERE clubname NOT IN (
    SELECT C.clubname
    FROM club C
    WHERE C.clubname NOT IN (
        SELECT clubname
        FROM club
        WHERE membername = 'Jim'
    )
)
AND membername <> 'Jim'
GROUP BY membername
HAVING COUNT(membername) = (
    SELECT COUNT(*)
    FROM club
    WHERE membername = 'Jim'
)

Thanks for all your help.

score 0 · Answer 2 · answered Mar 22 '15 at 04:23

If you have less than 32 clubs, assign each a binary flag value:

photography     1
cooking         2
woodworking     4
etc..  8, 16, 32, 64, 128, 256....

Then sum Jim's and sum their membership and check if all Jim's bits are set in that value (use bitwise AND)

score 0 · Accepted Answer · answered Mar 22 '15 at 05:11

0

you may do it in multiple ways, one way is to use where not exists like below:

SELECT DISTINCT C1.membername
FROM club C1 
WHERE NOT EXISTS (
    SELECT C2.membername
    FROM club C2 LEFT OUTER JOIN club C3 ON C2.clubname=C3.clubname 
    AND C3.membername=C1.membername
    WHERE C2.membername = 'Jim' AND C3.membername IS NULL
)
AND C1.membername !=  'Jim'

just tried it with T-SQL, here is the DEMO.

answered Mar 22 '15 at 05:11

void

7,760
3
25
43

you're method is the most efficient but I'm sorry to say that I don't fully understand why it works (I'm still a newbie) "C3.membername=C1.membername" <- what does this line do? – ssyc Mar 22 '15 at 07:18
for a specific membername(C1.membername), the subquery in "where not exists(...)" checks if there is a club for "Jim" which is not for the specific membername – void Mar 22 '15 at 07:22
and also pay attention to the left outer join in the subquery which lists the common clubs with "Jim"s club – void Mar 22 '15 at 07:24

score 0 · Answer 4 · answered Mar 22 '15 at 05:38

Brute force solution:

SELECT DISTINCT c1.membername
FROM Club c1 
WHERE c1.membername <> 'JIM' AND NOT EXISTS 
               (SELECT 1 FROM Club WHERE membername = 'JIM' AND  clubname NOT IN
                     (SELECT clubname FROM Club c2 WHERE c2.membername = c1.membername)
                 );

SQL Fiddle Demo (based on SQL Server)

The same code will work for most other RDBMS, since it uses only standard syntax.

SQL: Find the names of the people in at least the same clubs as one specific person

4 Answers4