Blessan Kurien Blessan Kurien - 6 months ago 13
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

story_id
is forigen key refers
id
in
stories
table.

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

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


Problem is i will get
totalcomment
value is 1 even if no comments for a particular post it should be 0 ,but i will get
count(storie.id)
result as 1

Answer

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 stories.id, 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.

SQL

SELECT stories.*,COUNT(Comments.story_id) as totalcomment
FROM stories LEFT JOIN comments ON stories.id=comments.story_id  GROUP BY stories.id, Comments.story_id