CODEWITHSUNDEEP
mysqljoinleft-join
Deepak Singh
Deepak Singh

Reputation: 650

Count in a multiple join statement

I have three tables Post: int id, varchar title Comment:int comment_id, int post_id, varchar comment Likes: int like_id, int post_id
Here post_id is a foreign key references table post.

I'm trying to get all the posts along with the count of comments and count of likes made on each post.

Here is what I have done so far:


    SELECT post.id, post.title, count(comment.id) as comments,

    FROM post

    left join comment on comment.post_id = post.id

    GROUP by post.id

This gives me the result for all the posts and count of comments made of each post. Now as soon as I add the next left join for likes table the count result return twice of it's actual value.

E.g If a post X had 2 comments and I add the like table join the count for the same post start giving me 4 as total count of comment on post X.

Help appreciated.

Upvotes: 0

Views: 57

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521249

I would write this using two separate subqueries to find the count of the comments and likes:

SELECT
    p.id,
    p.title,
    COALESCE(c.comment_cnt, 0) AS comments,
    COALESCE(l.like_cnt, 0) AS likes
FROM post p
LEFT JOIN
(
    SELECT post_id, COUNT(*) AS comment_cnt
    FROM comments
    GROUP BY post_id
) c
    ON p.id = c.post_id
LEFT JOIN
(
    SELECT post_id, COUNT(*) AS like_cnt
    FROM likes
    GROUP BY post_id
) l
    ON p.id = l.post_id;

Upvotes: 3

Related Questions