title: SQL query - getting arrival and departure stations based on time table
body: <p>I have the following tables:</p>
  
  <ul>
  <li><strong>ROUTE (id, train_number, station_id, arrival_time, departure_time)</strong> </li>
  <li><strong>SERVICE (train_number, train_name)</strong> </li>
  <li><strong>STATION (id, name, city)</strong></li>
  </ul>
  
  <p>A row in ROUTE table will contain the train number, it's arrival and departure time (or one of these two if the station is the first/last one) and a station_id.</p>
  
  <p>I would like to find out for each train_number, which paths it goes. My difficulty is in figuring out the arrival and departure station name for each route.</p>
  
  <p>For example train 1337 might have the following path:</p>
  
  <ul>
  <li>stationA -> stationB: 
  <ul>
  <li>route(1, 1337, stationA, null, 1800)</li>
  <li>route(2, 1337, stationB, 1900, 2000)</li>
  </ul></li>
  <li>stationB -> stationC:
  <ul>
  <li>route(3, 1337, stationC, 2020, 2100)</li>
  </ul></li>
  <li>stationC -> stationD:
  <ul>
  <li>route(4, 1337, stationD, 2120, 2200)</li>
  </ul></li>
  </ul>
  
  <p>I want to figure out these paths, i.e stationA -> stationB -> stationC etc.</p>
  
  <p>How is it achievable (I'm using Oracle SQL if that matters)?</p>
  
  <p>for stationA (getting the start of route) I could do the following:</p>
  
  <pre><code>SELECT name FROM STATION
  WHERE station.id = ROUTE.station_id AND ROUTE.arrival IS NULL
  </code></pre>