Joining multiple tables in MySQL: "Unknown column"

Question

I'm running into an error with a simple JOIN in MySQL. Am I missing something obvious?

SELECT stop_times.stop_sequence
FROM stop_times
INNER JOIN stops ON stop_times.stop_id = stops.stop_id
INNER JOIN trips ON stop_times.trip_id = trips.trip_id
WHERE stops.stop_code = :stopCode;

Where :stopCode is a parameter that is bound via PDO.

The error I'm getting is: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'stop_times.trip_id' in 'on clause''

The database schema for these three tables are:

stop_times
----------
trip_id VARCHAR(23) NOT NULL -- PK
arrival_time TIME NOT NULL
departure_time TIME NOT NULL
stop_id INT(10) UNSIGNED NOT NULL -- PK
stop_sequence INT(10) UNSIGNED NOT NULL -- PK


stops
-----
stop_id INT(10) UNSIGNED NOT NULL -- PK
stop_code INT(5) UNSIGNED NULL
stop_name VARCHAR(53) NOT NULL
stop_lat DECIMAL(8, 6) NOT NULL
stop_lon DECIMAL(9, 6) NOT NULL
stop_url VARCHAR(66) NULL

trips
-----
route_id INT(8) UNSIGNED NOT NULL
service_id VARCHAR(6) NOT NULL
trip_id VARCHAR(23) NOT NULL -- PK
trip_headsign VARCHAR(255) NOT NULL

I've also tried using aliases but that didn't work. Running the query in Microsoft Access works, the SQL that is produced has brackets around the JOIN statements though.

Why is my query not working?

Answer 1

Are you using back ticks (`` `)in your actual query to escape the table / column names, but stripping them out of your question? I'm guessing yes, because they probably gave you some formatting issues since Stack Overflow uses them to escape code blocks...

My guess is that you meant to write:

INNER JOIN `trips` ON `stop_times`.`trip_id` = `trips`.`trip_id`

But instead, you wrote:

INNER JOIN `trips` ON `stop_times.trip_id` = `trips`.`trip_id`

Since you're missing a pair of back ticks, it gets treated as a single column name, giving you the following error:

Unknown column 'stop_times.trip_id' in 'on clause': SELECT stop_times.stop_sequence FROM stop_times INNER JOIN stops ON stop_times.stop_id = stops.stop_id INNER JOIN trips ON stop_times.trip_id = trips.trip_id WHERE stops.stop_code = 1

SQL Fiddle Showing the Assumed Mistake