CODEWITHSUNDEEP
mysqlinner-joincombinations
CodeKingPlusPlus
CodeKingPlusPlus

Reputation: 16081

MySQL inner join on same table and pairs

Suppose I inner join a table on itself by one of the columns. I will have a one to many relationship on this field.

For example suppose I have the following table and call the columns col1 and col2 respectively.

a 1
b 1
c 2
d 1

Suppose I inner join the letters on the numeric column, col2. I would have the following:

a a
a b
a d
b b
b a
b d
c c
d d
d b
d a

I want to consider these results where order does not matter so (a, b) would be the same as (b, a). How can I change my query to return the following instead?

a a
a b
a d
b b
c c
d d

Here is my query so far:

select s1.col1, s2.col1 from table1 s1 inner join table1 s2 on s1.col2 = s2.col2

Note that this query gives the incorrect results as shown above. Thanks in advance!

Upvotes: 1

Views: 835

Answers (1)

unique2
unique2

Reputation: 2302

select s1.col1, s2.col1 
from table1 s1 inner join table1 s2 
  on s1.col2 = s2.col2 and s1.col1 <= s2.col1

Upvotes: 4

Related Questions