CODEWITHSUNDEEP
mysqljoincountleft-join
Piotr
Piotr

Reputation: 334

Count on joined table causes return of 1 row

I've got query like:

SELECT 
    b.title,
    b.url,
    b.`date`,
    b.gallery,
    count(c.id) as comments_count,
    a.name,
    b.content,
    b.comments,
    LEFT(b.content, LOCATE('<page>', b.content)-1) as content_short 
FROM blog b 
LEFT JOIN blog_comments c ON 
    (b.id = c.note AND c.approved = 1) 
LEFT JOIN administrators a ON 
    (b.aid = a.id) 
WHERE 
    b.`date` < now() AND 
    b.active = 1 
ORDER BY b.`date` DESC;

Now, when I remove count(c.id) as comments_count,, I've got 2 rows returned. When it's present, there's only 1 row returned.

Is there some way to fix ot or I simply have to change count(c.id) as comments_count, to (select count(id) ascomments_countfrom blog_comments where note = b.id) as comments_count,?

Upvotes: 0

Views: 145

Answers (1)

Gonzalo.-
Gonzalo.-

Reputation: 12682

Count(*) is an aggregated function, so it will apply in a group. That means that when you count on groups, it will apply the function on every group. The groups are formed when you use Group By, in this case, you're not using, so MySQL consider that ALL select (your joins) is ONLY 1 GROUP.

So, applies the count on the unique group and returning the count of rows.

you should add a Group by by the field you want

An example is here

Upvotes: 3

Related Questions