MySQL - Fetch row even if count is zero

[eluser]überfuzz[/eluser]
This isn't really a CI problem, but I try anyway.

I'm having two tables in a simple blogg I'm building. Right now I wanna fetch all headlines and count every comment made, to each headline.

Headline 1 comments 4
Headline 2 comments 1
Headline 2 comments 0 - This is where I mess up.
etc

I get gnarly when there's no comment made. This is my current sql:

Code:

SELECT
   blogg.ID AS ID,
   count('blogg_ID') AS comments,
   blogg.headline AS headline
FROM
   blogg
LEFT JOIN
   comments ON blogg.ID = comments.blogg_ID
WHERE
   activ = 1
GROUP BY
   ID

My problem is that depending on how I JOIN the tables I get a row if there is a match and if there's no match, no comment, I don't get that specific row. Right now I can't come up with an idea to a solution. Help would be appreciated!

[eluser]steelaz[/eluser]
I think you're using quotes incorrectly, field names have to quoted in backticks (``) or not at all. Try replacing

Code:

count('blogg_ID') AS comments,

with

Code:

count(comments.blogg_ID) as comments,

[eluser]überfuzz[/eluser]
Hunky Dory!!!

But... I don't get it, what's the difference? :-S

Would you mind enlighten me?

[eluser]mddd[/eluser]
count('blogg_id') is not counting any field of your database. It is just counting a piece of text.
You might just as well have written count('helloworld'). It would give the same result.

Btw, well found steelaz, I had totally missed that when I looked at the post.

[eluser]steelaz[/eluser]
You can also write it as

Code:

count(blogg_ID) AS comments,

just don't put single quotes around it, if you do, MySQL will treat it as regular string, not a column name.

[eluser]steelaz[/eluser]
@mddd, thanks, "blogg_ID" red color gave it away

[eluser]überfuzz[/eluser]
[quote author="steelaz" date="1273158832"]You can also write it as

Code:

count(blogg_ID) AS comments,

just don't put single quotes around it, if you do, MySQL will treat it as regular string, not a column name.[/quote]
I guess I have to state that if was quite a while since I tinkered with sql-querys. I know better that to mix up column names in different tables though. :cheese: