This is a simple case of relational-division. We have assembled an arsenal of possible techniques here:
While the queries in the currently accepted answer should work, these queries should be shorter and faster for each of your examples:
SELECT team_id -- returns team 1
FROM player_teams pt1
JOIN player_teams pt2 USING (team_id)
WHERE pt1.player_id = 1
AND pt2.player_id = 2;
SELECT team_id -- returns team 1 and 2
FROM player_teams
WHERE player_id = 2;
SELECT team_id -- returns team 2 (and 3 in the improved fiddle)
FROM player_teams
WHERE player_id = 3;
There is simply no need for JOIN
/ GROUP BY
/ HAVING
in cases 2 and 3.
If, however, you want to find teams that consist of player 3 only (and no other member), use this:
SELECT pt1.team_id -- returns team 3
FROM player_teams pt1
LEFT JOIN player_teams pt2 ON pt2.team_id = pt1.team_id
AND pt2.player_id <> pt1.player_id
WHERE pt1.player_id = 3
AND pt2.player_id IS NULL;
According to your definition this doesn't seem unlikely:
Given that each team may consist from 1 or 2 two players
Related:
SQL Fiddle with improved schema and extended test case.
Notably, I added FK constraints and replaced your UNIQUE
index with a PRIMARY KEY
constraint. More here:
Universal query
To make the same query work for either two player_id
or one player_id
and NULL
, use the NULL-safe comparison operator IS NOT DISTINCT FROM
:
SELECT pt1.team_id -- returns team 3
FROM player_teams pt1
LEFT JOIN player_teams pt2 ON pt2.team_id = pt1.team_id
AND pt2.player_id <> pt1.player_id
WHERE pt1.player_id = 3
AND pt2.player_id IS NOT DISTINCT FROM 2;
I added both cases to the fiddle. Might be a bit slower overall, and definitely slower than the simple query for just one player_id
.