I have the table matches

and the table teams

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

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!

CodePudding user response：

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)
)

CodePudding user response：

SELECT t1.id_team AS team1, t2.id_team AS AS team2
FROM   teams t1
JOIN   teams t2 ON t1.id_team > t2.id_team
LEFT   JOIN matches m ON (m.id_team1, m.id_team2) = (t1.id_team, t2.id_team)
                      OR (m.id_team1, m.id_team2) = (t2.id_team, t1.id_team)
WHERE  m.id_team1 IS NULL
ORDER  BY 1, 2;

The join condition t1.id_team > t2.id_team not only eliminates twice the same team, but also mirrored pairs.

See:

• Select rows which are not present in other table