MySQL Inner Join Query Syntax error

Question

I'm kind of a MySQL novice and can't figure out what is going wrong here. I have two tables. The left table is called Workouts. The relevant columns are date (type date) and id (type int).

The right table is called Workout_locations (relevant fields: workout_id type int, and location_id type int).

The join fields are Workouts.id and Workout_locations.workout_id.

All I want to do is get a table of two columns: date (from Workouts), and location_id (from Workout_locations). I need to only pull records from the Workouts table based on a couple of fields (the sql statement should make this clear).

Here is my sql syntax:

SELECT Workouts.date as date, Workout_locations.location_id as loc_id FROM Workouts WHERE Workouts.pacegroup_id='9' AND (Workouts.date BETWEEN '2013-08-19' AND '2013-08-25') INNER JOIN Workout_locations ON Workouts.id=Workout_locations.workout_id"

But I get this error:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INNER JOIN Workout_locations ON Workouts.id=Workout_locations.workout_id' at line 1

I'm hoping that this is a very easy error to spot for someone who is experienced with this. Can anyone see it? Thanks!

jterry · Accepted Answer · 2013-08-22 20:35:44Z

Your INNER JOIN should come before the WHERE. I also don't think you needed the parens around your BETWEEN clause, but I doubt that it'll cause an error either way:

SELECT Workouts.date as date, Workout_locations.location_id as loc_id FROM Workouts INNER JOIN Workout_locations ON Workouts.id=Workout_locations.workout_id WHERE Workouts.pacegroup_id = '9' AND Workouts.date BETWEEN '2013-08-19' AND '2013-08-25';

Also, though they technically let you get away with it, you should avoid using "date" as a selected column name (it's a reserved word).

You could do a bit of streamlining as well to make things a bit easier to read:

SELECT Workouts.date AS wo_date, Workout_locations.location_id AS loc_id FROM Workouts w INNER JOIN Workout_locations l ON w.id = l.workout_id WHERE w.pacegroup_id = '9' AND w.date BETWEEN '2013-08-19' AND '2013-08-25';

That seems to solve it. Yes, the parens are mostly for my own sanity. Thanks!

Collectives™ on Stack Overflow

MySQL Inner Join Query Syntax error

1 Answer 1

1 Comment

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Linked

Related