如何在同一个表中选择具有给定值的特定列中的两个不同的行

Question

I'm trying to select rows from a table that has details about stations of bus routes.

if took a one bus route all the stations between start and end stations are inserted in to this table.

what I'm trying to do is. show the passenger what bus route he/she should select when he/she wants to go to his destination from current place

to do so I'm getting his starting point (closest bus station) and destination

then I need to get all the bus routes numbers that has those two stations between the bus's route.

at some point there may be bus routes that only pass one station that passenger wants to go. Ex. there may be a bus that may pass only passenger's current location or her/his destination

those routes I don't want to show to the passenger.

From the below table I just need to show 168 and 163 route numbers if I search for start location = 'kotte junction' and end location = 'bangala junction' as 170 route is not going through the passenger destination.

+--------------------------------------------------------------------------------------+
| ID|route_number|station_name|station_order|added_date|CURRENT_TIMESTAMP|last_updater |
+--------------------------------------------------------------------------------------+
| |1|168|Kotahena|1|2017-05-28|2017-05-28 08:13:13|100024                              |
| |2|168|borella|2|2017-05-28|2017-05-28 08:13:13|100024                               |
| |3|168|kota road|3|2017-05-28|2017-05-28 08:14:20|100024                             |
| |4|168|Rajagiriya|4|2017-05-28|2017-05-28 08:14:20|100024                            |
| |5|168|kotte junction|5|2017-05-28|2017-05-28 08:15:46|100024                        |
| |6|168|mati ambalama|6|2017-05-28|2017-05-28 08:15:46|100024                         |
| |7|168|kotubamma|7|2017-05-28|2017-05-28 08:16:35|100024                             |
| |8|168|bangala junction|8|2017-05-28|2017-05-28 08:16:35|100024                      |
| |9|163|dehiwala|1|2017-05-28|2017-05-28 08:50:09|100024                              |
| |10|163|battaramulla|2|2017-05-28|2017-05-28 08:50:09|100024                         |
| |11|163|kotte junction|2|2017-05-28|2017-05-28 08:50:56|100024                       |
| |12|163|mati ambalama|3|2017-05-28|2017-05-28 08:50:56|100024                        |
| |13|163|kotubamma|4|2017-05-28|2017-05-28 08:51:42|100024                            |
| |14|163|bangala junction|5|2017-05-28|2017-05-28 08:51:42|100024                     |
| |15|170|kotte junction|1|2017-05-28|2017-05-28 09:04:47|100024                       |
| |16|170|mati ambalama|2|2017-05-28|2017-05-28 09:04:47|100024                        |
+--------------------------------------------------------------------------------------+

I tried flowing queries but didn't get what I exactly want.

SELECT * FROM `bus_route_stations` WHERE `station_name` = 'kotte junction' UNION ALL SELECT * FROM `bus_route_stations` WHERE `station_name` = 'bangala junction' 


SELECT * FROM bus_route_stations A
INNER JOIN bus_route_stations B
ON B.station_name = A.station_name
WHERE A.station_name = 'kotte junction' AND B.station_name = 'bangala junction'

SELECT * FROM bus_route_stations A INNER JOIN bus_route_stations B ON B.station_name = A.station_name WHERE A.station_name IN('kotte junction' ,'bangala junction')

Answer 1

SELECT A.route_number
    FROM bus_route_stations A
    JOIN bus_route_stations B  USING(route_number)
    WHERE A.station_name = 'kotte junction'
      AND B.station_name = 'bangala junction'
      AND A.station_order < B.station_order

It would be good to have this 'composite' index:

INDEX(station_name, route_number, station_order) -- in this order