Return teams that have never played each other - SQL

Question

I have the table matches

id_match	id_team1	id_team2
1	USA	JPN
2	AUS	USA
3	CAN	POL
4	POL	USA

and the table teams

id_team	name
USA	United States
JPN	Japan
...	...

And i want to return a new table of teams that have never played each other like this

id_team1	id_team2
USA	AUS
CAN	USA
...	...

I think i have to use cross join and a subtraction but i can't think exactly how to do it. if anyone can think of how to do it, or a simpler way, i'd really appreciate it!

score 0 · Accepted Answer · answered Nov 16 '22 at 00:40

You can cross join the teams table with itself to generate all possible combinations of matches, then exclude those that already happened with not exists:

If you want both team1/team2 and team2/team1 games, then:

select t1.id_team id_team1, t2.id_team id_team2
from teams t1
inner join teams t2 on t1.id_team != t2.id_team
where not exists (
    select 1 from matches m where m.id_team1 = t1.id_team and m.id_team2 = t2.id_team2
)

If you want a single match between each pair of teams, then we need to add some conditional logic:

select t1.id_team id_team1, t2.id_team id_team2
from teams t1
inner join teams t2 on t1.id_team < t2.id_team
where not exists (
    select 1 
    from matches m 
    where t1.id_team = least(m.id_team1, m.id_team2)
      and t2.id_team = greatest(m.id_team1, m.id_team2)
)

Thank you for replying! i forgot to say, and its important, that only want the combination to appear once — zaf, Nov 16 '22 at 00:47

Return teams that have never played each other - SQL

1 Answers1