SQL that counts from another table

Question

I manage a database for a social network where users can like, comment, and I created a table for each post, for example, 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 two columns likesand one comments, which counts the number of likes and comments each have post_id.

How can i do this?

Answer 1

You will need to join both tables.

SELECT post.*, COUNT(likes.id) AS like_count, COUNT(comment.id) AS comment_count
FROM post
    LEFT JOIN likes ON post.id = likes.post_id
    LEFT JOIN comment ON post.id = comment.post_id
GROUP BY post.id

Answer 2

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.

Answer 3

SELECT post.id, COUNT(like.id) AS like_count, COUNT(comment.id) AS comment_count
FROM post p
    LEFT JOIN like ON post.id = like.post_id
    LEFT JOIN comment ON post.id = comment.post_id
GROUP BY post.id

@Richard, select, group by select.

Answer 4

SELECT
  post.*,
  IFNULL(COUNT(like.user_id),0) AS likecount,
  IFNULL(COUNT(comment.user_id),0) AS commentcount
FROM
  post
  LEFT JOIN like ON post.post_id=like.post_id
  LEFT JOIN comment ON post.post_id=comment.post_id
GROUP BY
  like.post_id