2

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

  • query for player ids 1 and 2 should return team with id 1
  • query for player id 2 should return teams with ids 1 and 2
  • query for player id 3 should return team with id 3
lessless
  • 866
  • 10
  • 27
  • 1
    Thanks for creating fiddle and clearly explaining the scenarios. Accept the answer which worked for you by clicking on tick symbol on left of the answer so it can be closed. – Utsav Nov 25 '15 at 03:18

4 Answers4

2

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
Alex
  • 258
  • 1
  • 10
  • Thank you Alex, I missed third team indeed. That scenario covers case when there is only one player in the team, which is different to the second scenario where one player participate in two teams. – lessless Nov 25 '15 at 03:46
  • np, easy typo to make. the query i've posted will give the right answer in all three of those cases. – Alex Nov 25 '15 at 03:49
  • can you please explain the magic of the queries? is it in the count(*) statement? how does they work? – lessless Nov 25 '15 at 03:54
  • With count(*), you're saying "how many people from the set of players i've specified are members of each team?". You can see this interm result by running the query without the HAVING clause. After this result set has been computed, the HAVING clause drops any team where the total number of players found doesn't equal the number of players you're looking for. – Alex Nov 25 '15 at 03:59
  • Neat! I regret skipping an SQL class so much :( – lessless Nov 25 '15 at 04:02
1

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
Michael
  • 174
  • 8
  • pretty much except query when there are two players in the team: "OK, SQL give me a team id where both of those two players played together" – lessless Nov 25 '15 at 03:30
1

This is a simple case of . 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.

Community
  • 1
  • 1
Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
0

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
t1t1an0
  • 281
  • 1
  • 16
  • pretty much, how "team in which those two players are participating in"? – lessless Nov 25 '15 at 03:21
  • if you need to look for the team(s) given multiple players, you may change the data type of variable @player_id_to_search to a string and revise the criteria on the last line. – t1t1an0 Nov 25 '15 at 03:41