Colette Cleveland Colette Cleveland - 1 month ago 13
SQL Question

Getting distinct pairs from one table column

I'm trying to get a unique pair of values from a query in Oracle which basically looks like this:

select a.name, b.name
from table1 a
join table1 b on a.attribute = b.attribute


Now this should give me a bunch of repeated values like


  • apple,banana

  • orange,kiwi

  • banana,apple

  • kiwi,orange



but instead I just get


  • apple,apple

  • banana, banana

  • kiwi, kiwi

  • orange, orange



when I add

select a.name, b.name
from table1 a
join table1 b on a.attribute = b.attribute
where a.attribute < b.attribute


I get "No rows selected"

and I don't know what I'm doing wrong, please please help

Answer

You can omit joining the same name by using a.name <> b.name

select a.name, b.name from table1 a
join table1 b
on a.name <> b.name    
Comments