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

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!

CodePudding user response：

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.

CodePudding user response：

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

CodePudding user response：

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

CodePudding user response：

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