CODEWITHSUNDEEP
mysqlsql
Doss
Doss

Reputation: 13

How can I SELECT DISTINCT rows from two columns from the same table ignoring bidirectional similars?

I have a table flight as follows:

arrive      | depart
-------------------
New York    | London
New York    | Paris
Washington  | Rome
London      | New York
Rome        | Washington
New York    | Washington
New York    | London
Rome        | New York
Washington  | Rome

And I would like to produce a distinct / unique output based on the distinct from depart/arrive and arrive/depart - but not those that are bidirectionally the same. Therefore the outcome from above would be:

depart      | arrive
______________________
New York    | London
New York    | Paris
Washington  | Rome
New York    | Washington
Rome        | New York

Straightforward to get distinct basis depart/arrive, but can't get any JOIN, UNION etc. to ignore the bidirectional same (arrive/depart), but include bidirectional unique.

Pulling hair out, - need help. Thanks

Upvotes: 1

Views: 86

Answers (1)

GMB
GMB

Reputation: 222492

You can use a NOT EXISTS condition with a correlated subquery to remove duplicates.

However one thing to keep in mind is that, in order to get a consistent resultset, you need to define a rule to tell which of the two destinations is considered the depart, and which is the arrive. Since you did not provide a rule, I chose to have the value with lowest alphabetic order as depart.

Query:

SELECT
    LEAST(depart, arrive) depart,
    GREATEST(depart, arrive) arrive
FROM mytable t
WHERE NOT EXISTS (
    SELECT 1 
    FROM mytable t1
    WHERE t1.arrive = t.depart AND t1.depart = t.arrive AND t1.depart > t.depart
)

Demo on DB Fiddle:

| depart   | arrive     |
| -------- | ---------- |
| New York | Paris      |
| London   | New York   |
| Rome     | Washington |
| New York | Washington |
| New York | Rome       |

One other solution would be to pick the first occurence of the duplicate in the table, using primary key id, which, as per the comments, is available in the table:

SELECT depart, arrive
FROM mytable t
WHERE NOT EXISTS (
    SELECT 1 
    FROM mytable t1
    WHERE t1.arrive = t.depart AND t1.depart = t.arrive AND t1.id < t.id
)

Upvotes: 1

Related Questions