SQL join 3 tables while getting count from 2 of them

Question

Back with another SQL question about joins. I have 3 tables:

user: id, username, name, city, state, private
rides: id, creator, title, datetime, city, state
posts: id, title, user, date, state, city

I need to get the users from the user table, and based on the id of user, get the number of posts and rides for each person. Such as, user with id 25 has 2 rides and 4 posts, while the user with id 27 has 2 rides and 2 posts. The problem I am having, is that both users are coming back with 4 posts and rides each.

user.id = rides.creator = posts.user  //just so you know what fields equals the user id

Here is my code:

select u.id, u.username, u.state, u.city, count(p.id) as TotalPosts, count(r.id) as TotalRides
from user u
left join posts p on p.user=u.id
left join rides r on r.creator=u.id
where private='public'
group by u.id
order by u.username, u.state asc;

If I separate them out, and just join the posts or the rides, I get the correct totals back. I tried switching the order of the joins, but I got the same results. Not sure what is going on.

Any ideas or thoughts are appreciated.

Gordon Linoff · Accepted Answer · 2018-03-20 22:09:52Z

1

Your problem is a Cartesian product along two different dimensions. The best solution is to pre-aggregate the data:

select u.id, u.username, u.state, u.city, p.TotalPosts, r.TotalRides
from user u left join
     (select user, count(*) as totalposts
      from posts p
      group by user
     ) p
     on p.user = u.id left join
     (select creator, count(*) as totalrides
      from rides r
      group by creator
     ) r
     on r.creator = u.id
where u.private = 'public'
group by u.id
order by u.username, u.state asc;

answered Mar 20, 2018 at 22:09

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Darth Mikey D Over a year ago

Well, that didn't take long!! Thanks for the response. That worked. I'll mark the answer as correct as soon as the site lets me!!

Kevbo · Accepted Answer · 2018-03-20 22:20:06Z

0

you can always use a sub select.

select u.*, (select count(*) from posts where user = u.id) as 'posts', (select count(*) from rides where creator = u.id) as 'rides' from users u where .....

answered Mar 20, 2018 at 22:20

Kevbo

9439 silver badges12 bronze badges

Collectives™ on Stack Overflow

SQL join 3 tables while getting count from 2 of them

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related