CODEWITHSUNDEEP
sql
mosquito87
mosquito87

Reputation: 4440

WHERE condition for Intersection table

Let's assume I have the following intersection table MemberClub:

MemberID | ClubId
1        | 2
2        | 1
2        | 2
2        | 3

I'd like to get all members which are in the clubs with ID 1 and 2. So the result should be member with ID 2.

How would my where statement look like? I tried different variations but I'm not sure which one to choose.

Expected output should be:

MemberID | ClubId
2        | 1
2        | 2

I need a flexible version which works for a flexible number of clubs (it could be that I want all members being in club with just ID 1. Or all members being in club with ID 2, 4 and 6).

Upvotes: 0

Views: 66

Answers (1)

jarlh
jarlh

Reputation: 44746

General GROUP BY with HAVING COUNT(DISTINCT) solution:

select MemberID
from MemberClub
WHERE ClubId IN (1,2)
GROUP BY MemberID
HAVING COUNT(DISTINCT ClubId) = 2

I.e. make sure there are two different ClubId's for a user.

Alternatively, make sure there are different ClubId's for a user (works just in the two club case):

select MemberID
from MemberClub
WHERE ClubId IN (1,2)
GROUP BY MemberID
HAVING max(ClubId) <> min(ClubId)

Or skip the GROUP BY, do a self JOIN instead:

select distinct m1.MemberID
from MemberClub m1
    join MemberClub m2 on m1.MemberID = m2.MemberID
where m1.ClubId = 1
  and m2.ClubId = 2

To return members who are in all clubs:

select MemberID
from MemberClub
GROUP BY MemberID
HAVING COUNT(DISTINCT ClubId) = (select count(distinct ClubId) from MemberClub)

Upvotes: 1

Related Questions