Really simple MySQL JOIN not working?

Question

What am I doing wrong? Seriously confused.

SELECT *
FROM photos
WHERE user_id = 1
JOIN photos_albums ON photos_albums.photo_id = photos.id

The context is, I have a table to store photos, and another table to store photo albums (not shown). I also have a cross-referencing table photos_albums to store which photos are in which albums.

I get given a Syntax Error. eh?

Thanks!

Jack

Answer 1

The where should come at the end; try modifying it like this:

SELECT *
FROM photos p JOIN photos_albums pa ON pa.photo_id = p.id
WHERE p.user_id = 1

Answer 2

The WHERE clause must come after the joins, e.g.

SELECT *
FROM photos
INNER JOIN photos_albums 
  ON (photos_albums.photo_id = photos.id)
WHERE user_id = 1

See manual page for SELECT syntax for all the gory details.