How do you get the comment count of the post through mysql query?

Question

I would like to get numbers of comments for each posts using mysql.

I have two tables, one is post table and the other is comment table. Post table has id, title and content field while comment has id, post_id, author and comment field.

The result that would like to achieve is like

---------------------------------------------
Title               Comment Count
---------------------------------------------
My fancy post             2
---------------------------------------------

Let me know if you have confusion about my question.

Answer 1

SELECT
    a.title,
    COUNT(b.post_id) AS 'Comment Count'
FROM
    post a
LEFT JOIN
    comment b ON a.id = b.post_id
GROUP BY
    a.id

This will account for posts that don't have any comments.