Mysql查询在左连接中返回错误的结果(Mysql query return wrong result in left join)

Mysql查询在左连接中返回错误的结果(Mysql query return wrong result in left join)

我正在使用Codeigniter和MySQL。 我有两个表名作为information和review 。

这里我附上了我的information表的图片截图。

这是我的review表

这是我的info_img表

现在，我希望按照mytable information id获得完全没有review 。

在这里，我写了这样的查询：

SELECT info.*, info_img.name AS image, COUNT(rev.review_id) AS total FROM information AS info LEFT JOIN info_img ON info.information_id = info_img.information_id LEFT JOIN review AS rev ON rev.information_id = info.information_id WHERE info.status = 1 AND FIND_IN_SET('3', info.category) GROUP BY rev.information_id ORDER BY info.information_id ASC LIMIT 0 , 3

我得到18条评论information_id = 8 ，因为information_id = 8这是错误的。 而不是它我想将review_id information_id = 8评论数量计为6。

I am working with Codeigniter and MySQL. I have two table name as information and review.

Here I have attached image screenshot for my information table.

This is my review table

and this is my info_img table

Now, I want to get total no of review by information id as per mytable.

Here, I have written query like this :

SELECT info.*, info_img.name AS image, COUNT(rev.review_id) AS total FROM information AS info LEFT JOIN info_img ON info.information_id = info_img.information_id LEFT JOIN review AS rev ON rev.information_id = info.information_id WHERE info.status = 1 AND FIND_IN_SET('3', info.category) GROUP BY rev.information_id ORDER BY info.information_id ASC LIMIT 0 , 3

I got 18 review count for information_id = 8 that is wrong. Instead of it I want review count as 6 for information_id = 8.

最满意答案

我认为你所要做的就是增加你的数量......：

select info.*,info_img.name as image,count(distinct rev.review_id) as total from information as info left join info_img ON info.information_id = info_img.information_id left join review as rev ON rev.information_id = info.information_id WHERE info.status =1 AND FIND_IN_SET('3', info.category) GROUP BY rev.information_id order by info.information_id Asc LIMIT 0,3

它发生的原因可能是因为信息表将结果相乘（看起来是3）所以每个review_id将被计数3次，因此 - 计数不同

I think all you have to do is add distinct to your count.. :

select info.*,info_img.name as image,count(distinct rev.review_id) as total from information as info left join info_img ON info.information_id = info_img.information_id left join review as rev ON rev.information_id = info.information_id WHERE info.status =1 AND FIND_IN_SET('3', info.category) GROUP BY rev.information_id order by info.information_id Asc LIMIT 0,3

The reason its happening is probably because information table is multiplying the results(by 3 as it seems) so each review_id will be counted 3 times, therefor - count distinct