Mysql query return wrong result in left join

Question

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.

user5992977 · Accepted Answer · 2016-03-01 10:59:59Z

2

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

answered Mar 1, 2016 at 10:59

user5992977

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Mysql query return wrong result in left join

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related