shabir ullah shabir ullah - 2 months ago 7
MySQL Question

left join and count in same query return incorrect result

The problem is that if there is 0 comment or 1 comment the count shows 1 while the rest is working well means that 2, 3, etc working fine.
$sql = "SELECT blog.*,count(blog.id) as Total FROM blog left JOIN comment on comment.id = blog.id GROUP BY date desc";

Answer

Your query should look like this:

SELECT b.date, count(c.id) as Total
FROM blog b LEFT JOIN
     comment c 
     ON c.id = b.id
GROUP BY b.date DESC;

This assumes that date comes from blog (which should be the case if your current query is working). The difference is that you are counting from the second table, not the first.

This does not use * for columns from blog. That is usually a very, very bad idea when using GROUP BY. The best practice (enforced by almost all SQL engines) is to only include unaggregated columns in the SELECT when they are in the GROUP BY.

Note: It seems very awkward that the same column id is used for the JOIN between two very different entities (blogs and comments).