John Smith John Smith - 2 months ago 7
SQL Question

How to find queries that are same in each direction?

so I am a little rough on my sql and am trying to figure out how to get only the entries of the table that are same both on source and destination.

I created a small db here: http://sqlfiddle.com/#!9/05afc7/10

SELECT *
FROM Routes
INNER JOIN Cities src
ON (src.ID = Routes.Start)
INNER JOIN Cities dest
ON (dest.ID = Routes.Destination)
WHERE
[Not sure what to put here]


I am trying to find that if you have the route A->B and B->A, it will return those entries. So my query should return (1,3) and (3,1).

Answer

You were on the right track with doing a self-join, but your join condition had a problem. You want to retain routes whose end is also the start of another route.

In the query below, I select only the starts of each matching route, since the ends will be duplicates of some other start.

SELECT r1.Start,
       c.Name
FROM ROUTES r1
INNER JOIN ROUTES r2
    ON r1.Destination = r2.Start
INNER JOIN Cities c
    ON r1.Start = c.ID
WHERE r1.Start = r2.Destination

Demo here:

SQLFiddle