Blessan Kurien Blessan Kurien - 2 years ago 61
SQL Question

Getting total number of comments of post in mysql using count() always returns 1 even if no comment is present for particular post

I have two tables in my database

1.Stories (id,title,content)

2.Comments (id,story_id,comment) here

is forigen key refers

In order to get the comments of a particular post i am using this query

SELECT stories.*,COUNT( as totalcomment
FROM stories LEFT JOIN comments ON GROUP BY

Problem is i will get
value is 1 even if no comments for a particular post it should be 0 ,but i will get
result as 1

Answer Source

You need to count Comments.story_id and also group by Comments.story_id.

You have more than one comments for each stories, so your result goes always 1 when you group by that, you need to count the Comments.story_id so that for each stories it counts all the comments at once and also make a group by of it.


SELECT stories.*,COUNT(Comments.story_id) as totalcomment
FROM stories LEFT JOIN comments ON  GROUP BY, Comments.story_id 
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download