How to use aliases with MySQL LEFT JOIN

by

Don’t mix the comma operator with JOIN – they have different precedence! There is even a warning about this in the manual:

However, the precedence of the comma operator is less than of INNER JOIN, CROSS JOIN, LEFT JOIN, and so on. If you mix comma joins with the other join types when there is a join condition, an error of the form Unknown column 'col_name' in 'on clause' may occur. Information about dealing with this problem is given later in this section.

Try this instead:

SELECT *
FROM movies m
LEFT JOIN (
   stars s
   JOIN stars_in_movies sm
       ON sm.star_id = s.id
) ON m.id = sm.movie_id AND s.first_name LIKE '%Ben%'
LEFT JOIN (
    genres g
    JOIN genres_in_movies gm
        ON gm.genre_id = g.id
) ON gm.movie_id = m.id
WHERE m.title LIKE '%the%'
ORDER BY m.title ASC
LIMIT 5;

Leave a Comment