MySQL possible team vs. team matches where one player can be part of multiple teams

Question

What I have in a database is players and teams.

One player can join multiple teams as described in the table below:

player_id | team_id
1         | 1
2         | 1
2         | 2
2         | 3
3         | 2
3         | 3
4         | 4
5         | 4
6         | 5
7         | 5

What I'm looking to achieve is a table that shows possible team vs. team matches. Not all matches are possible since a player can't play against himself.

Example:

• Team 1 vs. Team 2 (Not possible since player 2 is in both teams)
• Team 1 vs. Team 3 (Not possible since player 2 is in both teams)
• Team 1 vs. Team 4 (Possible)

The resulting table could look like this:

Home | Away
1    | 4
4    | 1
1    | 5
5    | 1
5    | 2
5    | 3
5    | 4
2    | 5
3    | 5
4    | 5

I've tried to use a SELF JOIN with no luck:

SELECT A.team_id AS team_home, B.team_id AS team_away 
FROM players_teams A, players_teams B
WHERE A.team_id <> B.team_id AND A.player_id <> B.player_id

Answer 1

I think this does what you want:

select t1.team_id home, t2.team_id away
from players_team t1
inner join players_team t2 on t2.team_id <> t1.team_id
group by t1.team_id, t2.team_id
having max(t1.player_id = t2.player_id) = 0