SQLite query using data from two tables

Question

I have two tables: stop_times and trips -- both tables share trip_id. I want to build a query so that I extract information from both tables using trip_id, but then "filter" that information based on something from the first table.

I came up with this to get my information without the "filtering":

SELECT stop_times.trip_id, stop_times.arrival_time, trips.route_id
FROM stop_times, trips
WHERE stop_times.trip_id = trips.trip_id;

This works fine, but then I try to "filter" using stop_id:

SELECT stop_times.trip_id, stop_times.arrival_time, trips.route_id
FROM stop_times, trips
WHERE stop_times.stop_id IN (SELECT stop_id 
                             FROM stop_times 
                             WHERE stop_id = '81');

but it merges information incorrectly.

Mike Sherrill 'Cat Recall' · Accepted Answer · 2014-11-29 06:05:55Z

1

Why did you remove WHERE stop_times.trip_id = trips.trip_id from the second query? This might work.

SELECT stop_times.trip_id, stop_times.arrival_time, trips.route_id
FROM stop_times, trips
WHERE stop_times.trip_id = trips.trip_id
  AND stop_times.stop_id = 81;

But you'd be better off using ANSI join syntax.

SELECT stop_times.trip_id, stop_times.arrival_time, trips.route_id
FROM stop_times
INNER JOIN trips ON stop_times.trip_id = trips.trip_id
WHERE stop_times.stop_id = 81;

answered Nov 29, 2014 at 6:05

Mike Sherrill 'Cat Recall'

96.6k20 gold badges134 silver badges195 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

CL. Over a year ago

... stop_times JOIN trips USING (trip_id) ...

Collectives™ on Stack Overflow

SQLite query using data from two tables

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related