How can I get the id, min and max in the same My SQL query?

Question

I have 2 tables: stops and bus_route_details.

stop_id stop_name
      1 ‘C’ CROSS ROAD JUNCTION
      2 10TH ROAD
      3 16TH ROAD
      4 4TH ROAD (GOLIBAR)
      5 600TENAMENTGATE
      6 A.D.MODI INSTITUTE
      7 AHANSARI CHOWK
      8 A.H.SCHOOL
      9 A.P.M.COMPLEX
     10 A.T.I.
     11 AAI TULJABHAVANI CHOWK/LOKHANDWALA COMPLEX
     12 AAKASH GANGA SOCIETY (DHARAVI)

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.

bus_number stop_id stop_order
         8    2139         30
         8     351         31
         8    1791         32
         8      19         33
         8       2         34
         8     497         35
         8    2024         36
         8      20         37
         8     404         38
         8    1787         39
         8     621         40
         8    1937         41
         8    1941         42
         7     509          1
         7     788          2
         7     996          3
         7    1340          4
         7    1161          5
         7     335          6
         7    2296          7
         7     891          8

As per the above, 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)`
FROM bus_route_details
GROUP BY bus_number)
AND stops.stop_id = bus_route_details.stop_id

Any suggestions? I did the UNION and got all 4 results successfully in 2 columns but I would like 3 columns for this. Thank you

Answer 1

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