如何检查 mysql 搜索 JOIN 查询期间是否存在行？

Question

我有这个有效的 SQL 搜索查询，但只有当投票表中存在投票时它才有效。

mysql_query("SELECT m.Title, r.Subject, SUM(v.IsGood) AS Good, SUM(v.IsBad) AS Bad
    FROM Movies m
        JOIN Reviews r
            ON m.ID=r.MovieID
        JOIN Votes v
        ON r.ID=v.ReviewID
    WHERE (m.Title LIKE '%" . $search . "%' OR r.Subject LIKE '%" . $search . "%')
        GROUP BY m.Title, r.Subject
    ORDER BY SUM(v.IsGood) DESC, SUM(v.IsBad) ASC LIMIT 10")

所以我把它改成下面这样，这样无论有没有0票它仍然应该显示。但它没有任何结果，谁能告诉我我做错了什么？

mysql_query("SELECT m.Title, r.Subject, COUNT(v.ReviewID) AS Rvotes, SUM(v.IsGood) AS Good, SUM(v.IsBad) AS Bad
    FROM Movies m
        JOIN Reviews r
            ON m.ID=r.MovieID
        JOIN Votes v
        ON r.ID=v.ReviewID
    WHERE (m.Title LIKE '%" . $search . "%' OR r.Subject LIKE '%" . $search . "%')
        GROUP BY m.Title, r.Subject, v.ReviewID
            HAVING Rvotes >= 0
    ORDER BY SUM(v.IsGood) DESC, SUM(v.IsBad) ASC LIMIT 10")

Answer 1

假设即使投票表中不存在投票，您也希望得到结果，< code>LEFT JOIN 投票表。

mysql_query("SELECT m.Title, r.Subject, SUM(v.IsGood) AS Good, SUM(v.IsBad) AS Bad
    FROM Movies m
        JOIN Reviews r
            ON m.ID=r.MovieID
        LEFT JOIN Votes v
        ON r.ID=v.ReviewID
    WHERE (m.Title LIKE '%" . $search . "%' OR r.Subject LIKE '%" . $search . "%')
        GROUP BY m.Title, r.Subject
    ORDER BY SUM(v.IsGood) DESC, SUM(v.IsBad) ASC LIMIT 10")

注意：我已经修改了您的初始查询（不是第二个），因为您说它仍然没有提供您想要的内容。