mysql - 如何在 MySQL LEFT JOIN 中使用别名

Question

我的原始查询是使用 WHERE 子句而不是 JOIN 进行连接。我意识到这不是返回没有任何明星或流派的电影没有出现，所以我认为我必须进行 LEFT JOIN 才能展示每部电影。这是我的原始 SQL:

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

我尝试对电影进行 LEFT JOIN，但我肯定做错了什么。

SELECT *
FROM movies m, stars s, stars_in_movies sm, genres g, genres_in_movies gm
LEFT JOIN movies m1 ON m1.id = sm.movie_id
LEFT JOIN movies m2 ON m2.id = gm.movie_id
AND sm.star_id = s.id
AND gm.genre_id = g.id
ORDER BY m.title ASC
LIMIT 5;

我得到 ERROR 1054 (42S22): Unknown column 'sm.movi​​e_id' in 'on Clause' 很明显我做错了连接，我只是不明白它是什么。

Answer 1

不要将逗号运算符与 JOIN 混合使用 - 它们具有不同的优先级! 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.

试试这个:

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;