CODEWITHSUNDEEP
mysqlselect
random425
random425

Reputation: 719

Select distinct ordered pair from table join

Please consider two tables with names. They are joined by Table A id, so that two names get associated.
Is there a MySQL query that returns distinct pair of names regardless the order?

First table:

table_a
+-----------+--------------+
|    id     |     name     |
+-----------+--------------+
|     1     |     John     |
+-----------+--------------+
|     2     |     Jane     |
+-----------+--------------+ 
|     3     |     Jane     |
+-----------+--------------+
|     4     |     Sammy    |
+-----------+--------------+

Second Table:

table_b
+-----------+-------------------+-------------+
|    id     |     id_table_a    |    name     |
+-----------+-------------------+-------------+
|    1      |          1        |    Jane     |
+-----------+-------------------+-------------+
|    2      |          2        |    John     |
+-----------+-------------------+-------------+
|    3      |          3        |    Sammy    |
+-----------+-------------------+-------------+
|    4      |          4        |    Tara     |
+-----------+-------------------+-------------+

Desired result

(John, Jane)
(Jane, Sammy)  
(Sammy, Tara)

Thanks in advance!

Upvotes: 1

Views: 140

Answers (1)

sgeddes
sgeddes

Reputation: 62841

Here's one option using least and greatest:

select distinct least(a.name, b.name), greatest(a.name, b.name)
from table_a a 
  join table_b b on a.id = b.id_table_a

Upvotes: 2

Related Questions