GTFS data - stop data

Question

Feeling bit rusty about my SQL skills at the moment. What I would like to achieve is connect two SQL queries into one which give me basically all information about particular stop.

First query is simple one. It takes all stop informations. Piece of cake I would say ;)

    SELECT stop_id, stop_name, stop_lat, stop_lon, zone_id 
FROM stops WHERE stop_id = 97

Second one is bit more complicated but found my solution here and with small changes it's just like I want it.

select GROUP_CONCAT( distinct rt.route_short_name) as routes
from routes rt, trips tr, stop_times st
where rt.route_id = tr.route_id
and tr.trip_id = st.trip_id
and st.stop_id = 97

Now, I would like to add second query result as another column in first query result. Thanks to that after json_encode I will have nice JSON with all informations that I need!

Thanks!

Answer 1

You can do it with the hammer method:

SElECT * FROM (
   SELECT stop_id, stop_name, stop_lat, stop_lon, zone_id 
   FROM stops WHERE stop_id = 97
) a INNER JOIN (
   select st.stop_id, GROUP_CONCAT( distinct rt.route_short_name) as routes
   from routes rt, trips tr, stop_times st
   where rt.route_id = tr.route_id
   and tr.trip_id = st.trip_id
   and st.stop_id = 97
) b ON a.stop_id = b.stop_id

or by joining them:

select stops.stop_id, 
stops.stop_name, 
stops.stop_lat, 
stops.stop_lon, 
stops.zone_id, 
GROUP_CONCAT( distinct rt.route_short_name) as routes
from routes rt, trips tr, stop_times st, stops
where rt.route_id = tr.route_id
and tr.trip_id = st.trip_id
and st.stop_id = stops.stop_id
and st.stop_id = 97
group by st.stop_id