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)

CodePudding user response：

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]

CodePudding user response：

You could try filter for the stop name you need and counting for the number of stop

select bus_id 
from route  
stop_name 
group by bus_id
having count(distinct stop_name) = 2