Colette Cleveland Colette Cleveland - 1 month ago 17
SQL Question

getting distinct pairs from one table colmn

I'm trying to get a unique pair of values from a query and my query in oracle is basically

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