0

I have MySQL DB with bus route stops like: 

id | route_id | stop_name | stop_id| arrival_time | departure_time  | order
1  | 1        | Stop1     | 500002 | 00:00:00     | 14:20:00        | 2
2  | 1        | Stop2     | 502001 | 15:45:00     | 15:50:00        | 3
3  | 1        | Stop3     | 602001 | 16:38:00     | 16:43:00        | 4
4  | 1        | Stop4     | 600001 | 17:23:00     | 00:00:00        | 5
5  | 2        | Stop5     | 500002 | 00:00:00     | 12:40:00        | 2
6  | 2        | Stop6     | 502001 | 14:05:00     | 14:10:00        | 3
7  | 2        | Stop7     | 602001 | 14:58:00     | 15:03:00        | 4
8  | 2        | Stop8     | 600001 | 15:43:00     | 00:00:00        | 5

and would like to have information about start & stop grouped by route_id. The query is 

SELECT MIN(route_stops.order), MAX(route_stops.order), route_stops.route_id, route_stops.stop_name as departure ,latest.stop_name as arrival
FROM route_stops
JOIN (
  SELECT stop_name
  FROM route_stops rs
  WHERE **route_stops.route_id**=rs.route_id
  ORDER BY `order` DESC
  LIMIT 1
) latest
group by route_id

but unfortunately have a error saying #1054 - Unknown column 'route_stops.route_id' in 'where clause'

How do I can pass parent field into JOINed subquery ?

The originally what I expect from query is: 

route_id |from_stop_name|to_stop_name
1 | Stop1 | Stop4
2 | Stop5 | Stop8

Updates: found solutions works for me 

SELECT rs.route_id, rs.stop_name,rs.stop_id, j.stop_name,j.stop_id
FROM route_stops rs
INNER JOIN (
select d1.*
from `route_stops` d1
left outer join route_stops d2
on (d1.route_id = d2.route_id and d1.`order` < d2.`order`)
where d2.route_id is null
order by route_id
) j on j.route_id=rs.route_id
group by rs.route_id
Val KH
  • 19
  • 3
  • This isn't how SQL works (except for dependent subqueries). Pass the fields to join *outward* and let the query planner / RA do it's job. An outer WHERE on the 'inner' values (selected as additional columns in the derived join table) will work just fine, and will be 'optimized' by the query planner .. – user2864740 Jul 26 '15 at 08:12
  • See the duplicate question for a query to get the last stop name for each route ID. Then join with that as a subquery. – Barmar Jul 26 '15 at 08:14
  • Hi, thanks for clarification, but in provided example there is no one more field joined, containing information about Min value (see attached explanation about expected result) – Val KH Jul 26 '15 at 08:24

0 Answers0