Train Direct Route Query Let's say I've a table trainStations with the following:

 startStation  | endStation
 --------------+--------------
 SF            | Palo Alto
 Palo Alto     | SF
 Mountain View | Redwood City
 SF            | Palo Alto
 Palo Alto     | Mountain View
 Mountain View | SF


Now, I'm trying to learn how to do a round trip query.
For example, to query SF --&gt; PA I'd use:

SELECT * 
FROM   trainStations 
WHERE  startStation = 'SF' 
AND    endStation = 'Palo Alto'`


But how do I query for round trip (with no additional stops)? 

For example: SF--&gt; Palo Alto --&gt; SF

It should return the two lines:

 startStation  | endStation
 --------------+--------------
 SF            | Palo Alto
 Palo Alto     | SF


Only SF -&gt; Palo Alto followed by Palo Alto -&gt; SF rows are valid (direct route). The last three rows (in the table above) will not be selected because it's not a direct route (stop in Mountain View).

If this was code I'd do something like:

if (startStation[row num] == endStation[row num+1]) 
    &amp;&amp; (endStation[row num] == startStation[row num +1):
then print "found direct route"
I'd suggest you another approach:




create table TrainStations
(
    RouteId int, 
    OrderId int, 
    StartStation varchar(50), 
    EndStation varchar(50)
);



By adding Route and Order (Stops), you can get the whole journey of no matter what route.




    SELECT RouteId,
           OrderId,
           StartStation, 
           EndStation
      FROM TrainStations
     WHERE RouteId = 3
  ORDER BY RouteId, OrderId;



ROUTEID | ORDERID | STARTSTATION  | ENDSTATION   
------: | ------: | :------------ | :------------
      3 |       1 | SF            | Palo alto    
      3 |       2 | Palo alto     | Mountain View
      3 |       3 | Mountain View | SF           



dbfiddle here

Or if you want a single line:




    SELECT CONCAT(CONCAT (t1.StartStation, ', '), 
                  (SELECT LISTAGG(t2.EndStation, ', ') WITHIN GROUP (ORDER BY t2.OrderId)
                   FROM TrainStations t2
                   WHERE t2.RouteId = t1.RouteId)) Route
    FROM   TrainStations t1
    WHERE  t1.RouteId = 3
    AND    t1.OrderID = 1;



| ROUTE                            |
| :------------------------------- |
| SF, Palo alto, Mountain View, SF |



dbfiddle here