Geek Answers Handbook

MySQL Nested Queries

I am trying to write a query for a public transport system that will list the routes when I enter the stop numbers and destinations.

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 is an example of the 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 that will 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, in the above query, only route_numbers containing two stops will be returned, and not the routes along which the source (1) stops until the destination stops (3).

The query will return the following:

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

route_number 60 1 3, 3 1. -, , , , _ stop_number 1 , time_time stop_number 3.

. -Shain

+5
3

- :

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

, SQL- , , , .

+1
SELECT DISTINCT `route_number` R1 FROM R1.`route_timings` WHERE R1.`stop_number` =3 AND R1.`route_number` IN (
SELECT `route_number` R2 FROM `route_timings` WHERE R2.`stop_number`=1 AND R2.`arrival_time` > R1.`arrival_time`
);

, , . , , , 3, , 1 . SQLite, , SELECT route_number R2

+1

Using:

SELECT DISTINCT `route_number` FROM `route_timings` WHERE `stop_number` IN (1,3)

Note: why departure_time is> than arrival_time ??

0
source

More articles:


All Articles