7

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

NullPointer
  • 241
  • 1
  • 2
  • 7

2 Answers2

8

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
Roman Pekar
  • 107,110
  • 28
  • 195
  • 197
  • 6
    Small correction: To get the desired result specified in the question (combination rather than permutation), you need `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. – okdewit Sep 27 '18 at 09:22
1

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
ase
  • 13,231
  • 4
  • 34
  • 46
Dan Bracuk
  • 20,699
  • 4
  • 26
  • 43