Waqar Ahmed Waqar Ahmed - 1 month ago 5
MySQL Question

Group data with id and get data from first and last row

I have a database table

schedules
, which look like this:

enter image description here

Now, I am trying to get data such as:

----------------------------------------------
| id | bus_id | route | dept_time | arr_time |
----------------------------------------------
| 1 | 1 | 1, 4 | 07:00:59 | 23:30:30 |
----------------------------------------------


route
is just the collection of station_id which can be indexed using
route_index.
When
arr_time
is
NULL
, its mean it is the
departing station
and when
dept_time
is
NULL
, its mean, it is the
destination
. I have group the route with this query:

SELECT id,bus_id,GROUP_CONCAT(station_id SEPARATOR ', ') AS route FROM schedules GROUP BY bus_id;


But I don't know how to get the
arr_time
and
dept_time
using this query. Also, how to get
station
names instead of id in this query.
Station
table only contains
(id and name)
.

Answer

You can use a join to translate station IDs to their names. As for the arrival and departure times - it's a dirty trick, but since aggregate functions ignore nulls, you can use min/max to get them:

SELECT   sch.bus_id,
         GROUP_CONCAT(st.name ORDER BY route_index SEPARATOR ', ') AS route,
         MIN(dept_time) AS dept_time,
         MAX(arr_time) AS arr_time
FROM     schedules sch
JOIN     stations st ON sch.station_id = st.id
GROUP BY bus_id;
Comments