CODEWITHSUNDEEP
mysqlsqljoincount
user874737
user874737

Reputation: 533

Joining three tables with 2 count function

I've been at it for a day and couldn't really figure it out. I'm making a page where users can post and other users can reply and like.

I have these three tables: posts, replies, and likes. FYI that they all have a different number of rows, see full details below.

Posts table

post_id   message
-------   -------
1         This is post #1
2         This is post #2
3         This is post #3

Replies table

reply_id  post_id   message
-------   -------   -------
1         1        This is a reply to post #1
2         1        This is a reply to post #1
3         2        This is a reply to post #2
4         2        This is a reply to post #2
5         3        This is a reply to post #3

Likes table

like_id   post_id   liked
-------   -------   -------
1         1         Yes
2         1         Yes
3         1         Yes
4         2         Yes
5         2         Yes
6         3         Yes
7         3         Yes

Those are the structure of my tables. What I need to achieve is like this below:

All tables joined and tallied

post_id   total_replies   total_likes
-------   -------------   -----------
1         2               3     
2         2               2
3         1               2

Basically, for the 1st post, it should show that it has 2 replies and 3 likes. I cannot seem to do it using two counts. It is giving me incorrect numbers.

select posts.post_id, count(replies.post_id) as total_replies, count(likes.post_id) as total_likes from posts
inner join replies on posts.post_id = replies.post_id
inner join likes on posts.post_id = likes.post_id
group by posts.post_id

Upvotes: 0

Views: 45

Answers (2)

Somy
Somy

Reputation: 1624

I took your query and made a small edit so that it produces desired output. However note that the query would not be efficient. Gordon’s solution is more effective in terms of efficiency.

select 
     posts.post_id,
     count(distinct replies.reply_id) as total_replies,
     count(distinct likes.like_id) as total_likes 
from posts
 inner join replies on posts.post_id = replies.post_id
 inner join likes on posts.post_id = likes.post_id
group by 
  posts.post_id;

Or would be better to pre-aggregate your metrics. That way it would be bit more efficient.

select 
     posts.post_id,
     r.total_replies,
     L.total_likes 
from posts
 inner join (Select post_id, count(reply_id) as total_replies from replies group by post_id) r on posts.post_id = r.post_id
 inner join (Select post_id, count(like_id) as total_likes from likes group by posts_id) L on posts.post_id = L.post_id;

The caveat is you won’t get post that weren’t liked or replied upon since you are doing inner join. To get all posts irrespective of likes or replies you have to do left join.

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269803

I would suggest correlated subqueries:

select p.post_id,
       (select count(*)
        from replies r
        where r.post_id = p.post_id
       ) as total_replies,
       (select count(*)
        from likes l
        where l.post_id = p.post_id
       ) as total_likes
from posts p;

The problem with your query is that you are joining along two different dimensions, so you are getting a Cartesian product -- all likes and all replies for a given post.

Not only does this get rid of that problem, but with indexes on replies(post_id) and likes(post_id) this should have better performance than any solution that does an aggregation over all the data.

Upvotes: 2

Related Questions