Sukant Parvate Sukant Parvate - 1 year ago 59
SQL Question

Select Query with Count with more than three tables

I have following query

SELECT *, (SELECT COUNT(*) FROM post, comments WHERE comments.postid = as totalcom
FROM post, category
WHERE post.categoryid = category.catid

Then it returns entire table this is exactly what i needed but totalcom is showing same total to all post, it should show count of comments to the post and if result 0 then should show blank or null and anything else.

Please help

Answer Source

You have to use correlation to the post table of the main query:

       (SELECT COUNT(*) 
        FROM comments 
        WHERE comments.postid = as totalcom 
FROM post 
JOIN category ON post.categoryid = category.catid

Note: Always use modern, explicit JOIN syntax as in the above query, instead of old-fashioned implicit syntax.