How can I store the direction of a bus route in mysql database?

Question

I have 3 table: Route, Bus_stop, Route-Bus_stop
Route: id, weekday, weekend
Bus_stop: id, name
Route-Bus_stop: primary key(order, time), foreign key: idRoute, idBus_stop

Insert into Route(id,weekday,weekend) values(1,1,1);
Insert into Route(id,weekday,weekend) values(2,1,1);

Insert into Bus_stop(id,name) values(1,'A');
Insert into Bus_stop(id,name) values(2,'B');
Insert into Bus_stop(id,name) values(3,'C');

Insert into Route-Bus_stop(idRoute,idBus_stop,routeo_rder,stoptime) values(1,1,0,7:00);
Insert into Route-Bus_stop(idRoute,idBus_stop,route_order,stoptime) values(1,2,1,7:10);
Insert into Route-Bus_stop(idRoute,idBus_stop,route_order,stoptime) values(1,3,2,7:30);

Insert into Route-Bus_stop(idRoute,idBus_stop,route_order,stoptime) values(2,3,0,15:00);
Insert into Route-Bus_stop(idRoute,idBus_stop,route_order,stoptime) values(2,2,1,15:10);
Insert into Route-Bus_stop(idRoute,idBus_stop,route_order,stoptime) values(2,1,2,15:20); 
-- this is only an example, my database is bigger.

One of the route goes A -> B -> C, the other C->B->A
If the user wants to go A to C, how can I show only one of the routes with all of the bus stations to the destination?
I've write this query, but this shows both of the routes.

select *idRoute from Route-Bus_stop where idBus_stop=1 or idBus_stop=3 order by time; // A -> C

This will show route A->C and C-> A. But I want only the A->C

Answer 1

First don_'t rßuse reserved words like ORDER or TIME as column names, so you spre the need to use backticks all the tiome

Also Route-Bus_stop is also not valid without Backticks.

For your query you keed a selfjoin to get starting point and end point

CREATE TABLE Route_Bus_stop (
idRoute int
,idBus_stop int 
,`order` int 
,`time` varchar(10))

Insert into Route_Bus_stop(idRoute,idBus_stop,`order`,`time`) values(1,1,0,'7:00');
Insert into Route_Bus_stop(idRoute,idBus_stop,`order`,`time`) values(1,2,1,'7:10');
Insert into Route_Bus_stop(idRoute,idBus_stop,`order`,`time`) values(1,3,2,'7:30');

Insert into Route_Bus_stop(idRoute,idBus_stop,`order`,`time`) values(2,3,0,'15:00');
Insert into Route_Bus_stop(idRoute,idBus_stop,`order`,`time`) values(2,2,1,'15:10');
Insert into Route_Bus_stop(idRoute,idBus_stop,`order`,`time`) values(2,1,2,'15:20'); 

SELECT r1.idRoute,r1.idBus_stop,r2.idBus_stop,r1.`time`, r2.`time`
FROM Route_Bus_stop r1 INNER JOIN Route_Bus_stop r2
ON r1.idRoute = r2.idRoute AND r1.`order` < r2.`order`
WHERE r1.idBus_stop = 1 
AND r2.idBus_stop = 3

idRoute | idBus_stop | idBus_stop | time | time
------: | ---------: | ---------: | :--- | :---
      1 |          1 |          3 | 7:00 | 7:30

db<>fiddle here