CODEWITHSUNDEEP
sqldatabasejoincartesian
NullPointer
NullPointer

Reputation: 261

DB cartesian product on same table

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

Upvotes: 9

Views: 18840

Answers (2)

roman
roman

Reputation: 117380

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

Upvotes: 8

Dan Bracuk
Dan Bracuk

Reputation: 20804

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

Upvotes: 1

Related Questions