SELECT
games.id AS id,
games.ean AS ean,
games.title AS title,
games.developer AS developer,
reviews.NoReviews AS NoReviews
FROM
games
INNER JOIN
(SELECT
ean,
COUNT(*) AS NoReviews
FROM
reviews
GROUP BY ean) reviews
ON games.ean = reviews.ean
WHERE games.genre = genre
我对上面的代码有问题,仅当有评论的游戏时才返回数据,而当游戏没有评论时则不返回任何内容,这是我想要的
例如,如果某个游戏没有评论,它仍应返回该类型的游戏,但没有评论的计数为0。当我尝试更改任何内容时,即使有匹配项,也不会发送任何数据。因此,我已将其恢复为上面的工作方法。
最佳答案
尝试将您的Inner join
更改为left join
,内部联接将只为您提供匹配记录,而左联接将为您提供左表中的所有行,即使找不到匹配项
SELECT
games.id AS id,
games.ean AS ean,
games.title AS title,
games.developer AS developer,
reviews.NoReviews AS NoReviews
FROM
games
LEFT JOIN
(SELECT
ean,
COUNT(*) AS NoReviews
FROM
reviews
GROUP BY ean) reviews
ON games.ean = reviews.ean
WHERE games.genre = genre
关于mysql - 没有匹配项时返回计数,我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/21117891/