Nested MySQL Queries

Question

I have been trying to write a query for a public transportation system which will output a list of routes when I input the origin and destination stop numbers.

Here is my MySQL Table :

mysql> desc route_timings;
+----------------+---------+------+-----+---------+----------------+
| Field          | Type    | Null | Key | Default | Extra          |
+----------------+---------+------+-----+---------+----------------+
| ID             | int(11) | NO   | PRI | NULL    | auto_increment |
| route_number   | int(11) | NO   |     | NULL    |                |
| stop_number    | int(11) | NO   |     | NULL    |                |
| arrival_time   | time    | YES  |     | NULL    |                |
| departure_time | time    | YES  |     | NULL    |                |
+----------------+---------+------+-----+---------+----------------+
5 rows in set (0.00 sec)

Here are sample values :

mysql> select * from route_timings;
+----+--------------+-------------+--------------+----------------+
| ID | route_number | stop_number | arrival_time | departure_time |
+----+--------------+-------------+--------------+----------------+
|  1 |           54 |           1 | 10:00:00     | 10:05:00       |
|  2 |           54 |           2 | 11:00:00     | 11:05:00       |
|  3 |           54 |           3 | 12:00:00     | 12:05:00       |
|  4 |           55 |           3 | 13:00:00     | 13:05:00       |
|  5 |           55 |           4 | 14:00:00     | 14:05:00       |
|  6 |           55 |           5 | 15:00:00     | 15:05:00       |
|  7 |           60 |           3 | 10:00:00     | 10:05:00       |
|  8 |           60 |           2 | 11:00:00     | 11:05:00       |
|  9 |           60 |           1 | 12:00:00     | 12:05:00       |
+----+--------------+-------------+--------------+----------------+
9 rows in set (0.01 sec)

My question is: if I want to list the route_number's that would contain stop_number 1 and stop_number 3, I would write a query similar to this :

SELECT DISTINCT `route_number` FROM `route_timings` WHERE `route_number` IN (
    SELECT `route_number` FROM `route_timings` WHERE `stop_number`=1
) AND `route_number` IN (
    SELECT `route_number` FROM `route_timings` WHERE `stop_number`=3
);

However, the above query would only return the route_numbers containing the two stops and not the routes where the source stop (1) would arrive before the destination stop(3).

The query would return the following:

+--------------+
| route_number |
+--------------+
|           54 |
|           60 |
+--------------+

The route_number 60 does not start from 1 and go to 3 but instead starts from 3 and goes to 1. Can someone please help me add that bit to the query so that the query outputs only the route_numbers where the arrival_time for the stop_number 1 is less than the arrival_time for the stop_number 3.

Thanks in advance. -Shain

Answer 1

How about something like:

select fromStop.RouteNumber  
from routeTimings fromStop  
  inner join routeTimings toStop on toStop.RouteNumber = fromStop.RouteNumber and toStop.StopNumber = 3 and toStop.ArrivalTime > fromStop.DepartureTime  
where fromStop.StopNumber = 1

Note this is using SQL server syntax and names adjusted to the convention I'm used to but the principle should be clear