I have 2 tables: stops and bus_route_details.
The table stops simply stores the id and name of each stop. The bus_route_details table stores the bus_number, the stop_id of the stop from the stops table and the order in which the stop appears on that route. The first stop has the order 1 whereas the last stop can be a number like 44 if the route has 44 stops in total.
As per the Image attached, I would like to get a table that shows where bus number 7 and 8 start and end.
In simple words, get the distinct list of bus numbers, find the stop which minimum stop order and maximum stop order for each bus.
7, start, end [This is how I want the bus numbers and stop names]
My current query only gives me 2 columns: either the starting or the ending stop_name. How can I retrieve all 3 columns in the same query?
SELECT bus_number, stop_name from bus_route_details, stops `WHERE(bus_number, stop_order) IN (SELECT bus_number, MAX(stop_order)`
GROUP BY bus_number)
AND stops.stop_id = bus_route_details.stop_id
One way of doing this is to find the min and max in a derived table and join the stops table and finally use conditional aggregation to flatten the result, like this:
select b.Bus_number, max(case when b.stop_order = x.mio then b.stop_id end) min_stop_id, max(case when b.stop_order = x.mio then s.stop_name end) min_stop_name, max(case when b.stop_order = x.mao then b.stop_id end) max_stop_id, max(case when b.stop_order = x.mao then s.stop_name end) max_stop_name from bus_route_details b join ( select Bus_number, min(stop_order) mio, max(stop_order) mao from bus_route_details group by Bus_number ) x on b.Bus_number = x.Bus_number and (b.stop_order = x.mio or b.stop_order = x.mao) join stops s on b.stop_id = s.stop_id or b.stop_id = s.stop_id group by b.Bus_number;
This would give you a result like:
Bus_number min_stop_id min_stop_name max_stop_id max_stop_name ----------- ----------- -------------------- ----------- -------------------- 7 509 stop 1 891 stop 2 8 351 stop 3 1941 stop 4