shabir ullah shabir ullah - 1 year ago 64
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( as Total FROM blog left JOIN comment on = GROUP BY date desc";

Answer Source

Your query should look like this:

SELECT, count( as Total
     comment c 
     ON =

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).

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download