get all comments including its votes for a post

Question

I have following three tables in mysql.

postid | post_content => posts

commentid | post | comment_content => comments

commentvoteid | comment | voter => comment_votes

I want to fetch all the comments, counting its votes for a post.

commentid | comment_content | comment_votes => my_result

I have tried the following query but not getting the desired result.

SELECT commentid,comment_content,count_comments.total AS comment_votes
FROM comments
INNER JOIN(SELECT COUNT(*) AS total FROM comment_votes WHERE comment=comments.commentid) AS count_comments
WHERE post={$postId}

Is it possible to fetch the result as I wanted? How can I do that?

Answer 1

You can use GROUP BY to achieve what you want:

SELECT commentid,comment_content,COUNT(*) AS total
FROM comments
INNER JOIN comment_votes ON (comment_votes.comment=comments.commentid)
WHERE post={$postId}
GROUP BY commentid;

Answer 2

The method that you are trying uses a correlated subquery. You can do this, but the correlated subquery needs to go into the select clause:

SELECT c.commentid, c.comment_content,
       (SELECT COUNT(*) FROM comment_votes cv WHERE cv.comment = c.commentid
       ) AS comment_votes
FROM comments c
WHERE post={$postId};

Normally, I much prefer the group by approach but sometimes in MySQL this can be faster.

Answer 3

maybe like this:

select a.commentid, a.comment_content, count(b.*) as total from (
select commentid, comment_content from comments where post={$postId}) a
join comment_votes b on b.comment = a.commentid
group by a.commentid, a.comment_content;