CODEWITHSUNDEEP
sqljoin
Darth Mikey D
Darth Mikey D

Reputation: 109

SQL join 3 tables while getting count from 2 of them

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.

Upvotes: 1

Views: 213

Answers (2)

Kevbo
Kevbo

Reputation: 943

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 .....

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1271191

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;

Upvotes: 1

Related Questions