CODEWITHSUNDEEP
sqlsqlitegtfs
hardika
hardika

Reputation: 642

SQL for how to Get next stations based on station_id in GTFS data

This is my SQL query

SELECT 
Routes.route_id, Routes.route_desc, Routes.route_type, 
Trips.service_id, Trips.trip_id, Trips.route_direction, 
Stop_times.stop_id, Stop_times.arrival_time, Stop_times.departure_time, 
Stops.stop_name, Calendar.start_date, Calendar.end_date
FROM Trips 
INNER JOIN Routes ON Trips.route_id = Routes.route_id
INNER JOIN Stop_times ON Trips.trip_id = Stop_times.trip_id 
INNER JOIN Stops ON Stop_times.stop_id = Stops.stop_id
INNER JOIN Calendar ON Trips.service_id = Calendar.service_id
WHERE 
Calendar.start_date = strftime('%Y%m%d','now') and
CAST(REPLACE(Stop_times.arrival_time, ':', '') as decimal)  
         >= strftime('%H%M%S', 'now', 'localtime')
and Trips.direction_id = 0
and Stop_times.stop_id = '279018' 
ORDER BY Stop_times.arrival_time asc

1> I have resulted as like this

(many other columns as well)

2>I want to use the first-row trip_id and put that as 

SELECT 
Routes.route_id, Routes.route_desc, Routes.route_type, 
Trips.service_id, Trips.trip_id, Trips.route_direction, 
Stop_times.stop_id, Stop_times.arrival_time, Stop_times.departure_time, 
Stops.stop_name, Calendar.start_date, Calendar.end_date
FROM Trips 
INNER JOIN Routes ON Trips.route_id = Routes.route_id
INNER JOIN Stop_times ON Trips.trip_id = Stop_times.trip_id 
INNER JOIN Stops ON Stop_times.stop_id = Stops.stop_id
INNER JOIN Calendar ON Trips.service_id = Calendar.service_id
WHERE 
Calendar.start_date = strftime('%Y%m%d','now') and
CAST(REPLACE(Stop_times.arrival_time, ':', '') as decimal)  
         >= strftime('%H%M%S', 'now', 'localtime')
and Trips.direction_id = 0
and Stop_times.trip_id = 'what ever on first row from above query example 551.22018xxxxx' 
ORDER BY Stop_times.arrival_time asc

The result should be as following

Thanks in Advance.

Upvotes: 0

Views: 219

Answers (1)

dwir182
dwir182

Reputation: 1549

You can use subquery to wrap your result and use the result to execute your filter.

Your query should be like this :

SELECT *
FROM(
SELECT 
    Routes.route_id, Routes.route_desc, Routes.route_type, 
    Trips.service_id, Trips.trip_id, Trips.route_direction, 
    Stop_times.stop_id, Stop_times.arrival_time, Stop_times.departure_time, 
    Stops.stop_name, Calendar.start_date, Calendar.end_date
 FROM Trips 
    INNER JOIN Routes ON Trips.route_id = Routes.route_id
    INNER JOIN Stop_times ON Trips.trip_id = Stop_times.trip_id 
    INNER JOIN Stops ON Stop_times.stop_id = Stops.stop_id
    INNER JOIN Calendar ON Trips.service_id = Calendar.service_id
 WHERE 
    Calendar.start_date = strftime('%Y%m%d','now') and
    CAST(REPLACE(Stop_times.arrival_time, ':', '') as decimal)  
             >= strftime('%H%M%S', 'now', 'localtime')
    and Trips.direction_id ={}
    and Stops_Time.Stop_ID = 'xxxx' 
ORDER BY Stop_times.arrival_time ASC)
WHERE Trip_ID = @Trip_ID

EDIT :

You need just 1 row the first in the Stop_Times.Stop_ID so wrap the same condition with your select first and add limit

 SELECT 
    Routes.route_id, Routes.route_desc, Routes.route_type, 
    Trips.service_id, Trips.trip_id, Trips.route_direction, 
    Stop_times.stop_id, Stop_times.arrival_time, Stop_times.departure_time, 
    Stops.stop_name, Calendar.start_date, Calendar.end_date
 FROM Trips 
    INNER JOIN Routes ON Trips.route_id = Routes.route_id
    INNER JOIN Stop_times ON Trips.trip_id = Stop_times.trip_id 
    INNER JOIN Stops ON Stop_times.stop_id = Stops.stop_id
    INNER JOIN Calendar ON Trips.service_id = Calendar.service_id
 WHERE 
    Calendar.start_date = strftime('%Y%m%d','now') and
    CAST(REPLACE(Stop_times.arrival_time, ':', '') as decimal)  
             >= strftime('%H%M%S', 'now', 'localtime')
    and Trips.direction_id ={}
    and Stop_Times.Trip_ID = 
        (SELECT Stop_Times.Trip_ID
         FROM Trips
            INNER JOIN Stop_times ON Trips.trip_id = Stop_times.trip_id 
            INNER JOIN Stops ON Stop_times.stop_id = Stops.stop_id
            INNER JOIN Calendar ON Trips.service_id = Calendar.service_id
          WHERE
            Calendar.start_date = strftime('%Y%m%d','now') and
            CAST(REPLACE(Stop_times.arrival_time, ':', '') as decimal)  
                   >= strftime('%H%M%S', 'now', 'localtime')
            and Trips.direction_id ={}
          ORDER BY Stop_times.arrival_time ASC LIMIT 1) 
 ORDER BY Stop_times.arrival_time ASC

Upvotes: 1

Related Questions