there are two models Player and Team which relates as Many-to-Many to each other, so schema contains three tables players, player_teams and teams.
Given that each team may consist from 1 or 2 two players, how to find a team by known player id(s)?
In this SQLFiddle http://sqlfiddle.com/#!15/27ac5
There's a mistake in the third bullet point of your problem statement, I think. There is no team 3. In that third case, I think you want to return team 2. (The only team that player 3 is on.)
This query requires 2 bits of information - the players you are interested in, and the number of players.
SELECT team_id, count(*)
FROM players_teams
WHERE player_id IN (1,2)
GROUP BY team_id
HAVING count(*) = 2
-- returns team 1
SELECT team_id, count(*)
FROM players_teams
WHERE player_id IN (2)
GROUP BY team_id
HAVING count(*) = 1
-- returns teams 1 & 2
SELECT team_id, count(*)
FROM players_teams
WHERE player_id IN (3)
GROUP BY team_id
HAVING count(*) = 1
-- returns team 2
edit: here's an example of using this via ruby, which maybe makes a little clearer how it works...
player_ids = [1,2]
sql = <<-EOF
SELECT team_id, count(*)
FROM players_teams
WHERE player_id IN (#{player_ids.join(',')})
GROUP BY team_id
HAVING count(*) = #{player_ids.size}
EOF
Is this what you are looking for?
select t.name
from teams t
inner join players_teams pt on t.id = pt.team_id
where pt.player_id = 1
-- "OK, SQL give me a team id where both of those two players played together"
select pt1.team_id
from players_teams pt1
inner join players_teams pt2 on pt1.team_id = pt2.team_id
where pt1.player_id = 1
and pt2.player_id = 2
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:
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.
you may try the code below:
DECLARE @player_id_to_search INT
SET @player_id_to_search = 1
SELECT DISTINCT teams.name
FROM teams
JOIN players_teams ON players_teams.team_id = teams.id
JOIN players ON players.id = players_teams.player_id
AND players.id = @player_id_to_search
