Query rows and include rows with columns reversed-CodePudding

I'm trying to query a table. I want the results to include the FROM and TO columns, but then also include rows with these two values reversed. And then I want to eliminate all duplicates. (A duplicate is the same two cities in the same order.)

For example, given this data.

Trips

FROM                 TO
-------------------- --------------------
West Jordan          Taylorsville
Salt Lake City       Ogden
West Jordan          Taylorsville
Sandy                South Jordan
Taylorsville         West Jordan

I would want the following results.

West Jordan          Taylorsville
Taylorsville         West Jordan
Salt Lake City       Ogden
Ogden                Salt Lake City
Sandy                South Jordan
South Jordan         Sandy

I want to do this using C# and Entity Framework, but I could use raw SQL if I need to.

Is it possible to do this in a query, or do I need to manually perform some of this logic?

CodePudding user response：

Not sure if I'm following, but doesn't just a simple union work for your sample?

select from, to
  from some_table
 union
select to, from
  from some_table

CodePudding user response：

I am just writing this on the fly but I do believe the following should handle the first part of your question:

SELECT * 
FROM Trips
WHERE ID IN (
    SELECT ID 
    FROM Trips t1
    INNER JOIN Trips AS t2
    ON t2.To = t1.From AND t2.From = t1.To
)

To then delete all duplicates and keep only one copy this should work:

SELECT * 
FROM Trips
WHERE ID NOT IN 
(
    SELECT MIN(ID)
    FROM Trips
    GROUP BY [From], [To]
)

I am assuming there is more to the table than just those fields. Usually you have a field (primary key) to uniquely identify the row. I am using ID for that field, replace with whatever your table is using.