Search code examples
mysqlsqldatabasephpmyadminxampp

SQL query to find the bus travelling through route

Having a table name route, contains the bus_id,stop_name and position(it is the sequence of the stops).

bus travel in one way according to position


Table: route 

| bus_id | stop_name | position |
|--------|-----------|----------|
| 1      | Stop_1    | 1        |
| 1      | Stop_2    | 2        |
| 1      | Stop_3    | 3        |
| 1      | Stop_4    | 4        |
| 1      | Stop_5    | 5        |
| 1      | Stop_6    | 6        |
| 1      | Stop_7    | 7        |
| 2      | Ramdom_1  | 1        |
| 2      | Ramdom_2  | 2        |
| 2      | Stop_3    | 3        |
| 2      | Stop_4    | 4        |
| 2      | Stop_5    | 5        |
| 2      | Stop_6    | 6        |
| 2      | Ramdom_3  | 7        |

Now need to find the bus_id which go from stop_3 to stop_6 i.e bus_id = 1 and 2

examples:\

  1. from stop_1 to stop_6 = 1\
  2. from stop_6 to Ramdom_3 = 2\
  3. from stop_6 to stop_1 = no bus found\

Need to MYSQL query to find the above data

database used Server version: 10.4.21-MariaDB (xamp)

Solution 
  • Select a.*, b.* 
From route a
Join route b on a. bus_id=b.bus_id
Where a.position <b.position
And a.name=[stopname] and b.name=. 
[stopname]