CODEWITHSUNDEEP
mysqlsql
equanimity
equanimity

Reputation: 2533

SQL: how to get membership based on ALL from a given cohort

I have the following tables in a MySQL database:

team

team_id   name
3         Rangers
12        Capitals
19        Red Wings
4         Bruins
212       Avalanche
102       Flyers
20        Islanders
50        Sabres
7         Stars

player

id    name
2     Zach
1     Deb
17    William
9     Viktor
12    Andrew
41    Chris
22    Bobby
5     Phil
3     Roy
92    Li
6     Juan

players_in

team_id   player_id   points
3         2           42
212       2           19
3         12          18
19        12          2
3         41          2
4         41          1
212       41          78
212       17          1
19        41          4
12        41          2
3         17          6
4         1           9
102       1           40
102       22          7
20        22          19
20        5           22
50        3           20
12        92          15
12        17          8
7         6           12

Here is a SQL Fiddle with the data: http://www.sqlfiddle.com/#!9/989ebe/1

I would like to get the name and id of the players who have played on ALL of the teams that Zach has played on.

In this case, Zach has played for the Rangers and the Avalanche.

Therefore, the desired result set would be:

name       id
William    17
Chris      41

(because these players were part of both the Rangers and the Avalanche teams)

How would I do this?

Thanks!

Upvotes: 1

Views: 71

Answers (3)

Trung Duong
Trung Duong

Reputation: 3475

Your requirement could be translated to: searching for players which there's not exists any Jack's team that they don't play in. Corresponding query could be:

SELECT
  DISTINCT p1.name, p1.id
FROM
  player p1 
  INNER JOIN players_in pin1 ON p1.id = pin1.player_id
WHERE
  name != 'Zach'
  AND NOT EXISTS (
    SELECT 1
    FROM 
      team t 
      INNER JOIN players_in pin2 ON t.team_id = pin2.team_id
      INNER JOIN player p2 ON p2.id = pin2.player_id
    WHERE 
      p2.name = 'Zach' 
      AND NOT EXISTS (SELECT 1 
                      FROM players_in pin3 
                      WHERE pin2.team_id = pin3.team_id 
                        AND pin1.player_id = pin3.player_id)
  );

Demo: http://www.sqlfiddle.com/#!9/989ebe/61

Upvotes: 1

Luca Riccitelli
Luca Riccitelli

Reputation: 374

select distinct p.* 
from player p
  join players_in pi on pi.player_id = p.id
  join player p2 on p2.name = 'Zach'
  join players_in pi2 on pi2.team_id = pi.team_id
                and pi2.player_id = p2.id
where
  p.name <> 'Zach'
  and not exists (select 1 from players_in pi3                   
                  where pi3.player_id = p2.id
                      and pi3.team_id not in (select team_id 
                                              from players_in pi4 
                                              where pi4.player_id = p.id));

First of all I've joined players_in (pi) with players (p) obtaining the set of all players and theirs teams.

Second, cross joined player zack joined with player_in (pi2) obtaining the set of Zach's teams. Joined pi2 with pi I've obtained the set of all player that had played in a Zach's team.

Now the where conditions:

  • p.name <> 'Zach' will exclude Zach from my list.
  • The not exists condition is the hard part of the query. I've selected all Zach teams again (pi3) not in the set of the player's (p) team,

SQL Fiddle here

Upvotes: 1

Ajax1234
Ajax1234

Reputation: 71451

Using a cte for Zach's games and then checking all potential memberships based on team_id existence in the cte's values:

with cte as (
   select pi1.team_id from players_in pi1 join player p2 on p2.id = pi1.player_id 
   where p2.name = 'Zach'
)
select p.* from player p where (select count(*) from cte c) = (select 
    sum(pi1.team_id in (select c.team_id from cte c)) 
    from players_in pi1 where pi1.player_id = p.id) and p.name != 'Zach'

See fiddle.

Upvotes: 0

Related Questions