I have a one column table with three rows as below:
col1
team1
team2
team3
team4
I want to do a self cartesian join with result as below:
team1, team2
team1, team3
team1, team4
team2, team3
team2, team4
team3, team4
I have a one column table with three rows as below:
col1
team1
team2
team3
team4
I want to do a self cartesian join with result as below:
team1, team2
team1, team3
team1, team4
team2, team3
team2, team4
team3, team4
cartesian product
is cross join
in DB terms, you can remove rows where teams are equal in where
clause:
select
t1.col1, t2.col1
from teams as t1
cross join teams as t2
where
t1.col1 <> t2.col1
You can join both tables together for the intended output like this:
select t1.col1, t2.col1
from table t1
join table t2
on t1.col1 <> t2.col1
© 2022 - 2024 — McMap. All rights reserved.
where t1.col1 > t2.col1
. Otherwise you wouldn't get (1,1), but you'd still get both (1,2) and (2,1) when you just want HALF of the Cartesian product for all combinations. – Pluckless