Make a list of stations by checking next_station column value How to list all station between selected two stations based on next_station_id

For instance, if the user selects origin A station and destination to J than I want to list all stations in ORDER that have set into the next_station_id column.

I have an intermediate level of knowledge in MySQL and have no idea how to write such a query.

SELECT r.number as route, s.name AS station, s.code AS station_code, rl.name AS line, s.line_two_id AS interchange
FROM stations AS s
         JOIN route_stations as rs
              ON rs.station_id = s.id
         JOIN routes r
              ON rs.route_id = r.id
         JOIN route_lines rl
              ON s.line_id = rl.id
ORDER BY rs.station_order ASC;



  I know the above query is just to join table but as I said I have no
  clue how to write a query to get a list of stations that have been set
  in next_station_id


route_stations table



I am also adding a diagram in case anyone wants to look the structure to refer and  suggest

Diagram
WITH RECURSIVE 
cte AS ( SELECT station, next_station, 1 route_order
         FROM routes
         WHERE station = @source
           AND route_id = @route_id
         UNION ALL
         SELECT cte.next_station, routes.next_station, cte.route_order + 1
         FROM routes, cte
         WHERE routes.station = cte.next_station 
           AND cte.next_station != @source
           AND cte.station != @destination
           AND routes.route_id = @route_id )
SELECT * 
FROM cte
WHERE @destination = ( SELECT station 
                       FROM cte 
                       ORDER BY route_order DESC LIMIT 1 )
;


Wrong route selection (including cycles) allowed - in such case the resultset will be empty.