SQL that counts from another table

Question

I am managing a database for a social network where users can like, comment and I created a table each for the post, like, and comments.

like --- post_id
         user_id

comment --- post_id
            user_id

Now when I run select * from post, I want to be able to add a two columns likes and comments that counts the number of likes and comments having each post_id.

How can I do this?

Answer 1

Like this:

SELECT *
,    (SELECT COUNT(*) FROM likes WHERE post_id=p.id) as LIKE_COUNT
,    (SELECT COUNT(*) FROM comment WHERE post_id=p.id) as COMMENT_COUNT
FROM post p

Demo on sqlfiddle.