Left Join 2 tables on 1 table

Question

It must be pretty easy, but i can't think of any solution nor can I find an answer somewhere...

I got the table 'users'
and one table 'blogs' (user_id, blogpost)
and one table 'messages' (user_id, message)

I'd like to have the following result:

User | count(blogs) | count(messages)  
Jim | 0 | 3  
Tom | 2 | 3  
Tim | 0 | 1  
Foo | 2 | 0

So what I did is:

SELECT u.id, count(b.id), count(m.id) FROM `users` u  
LEFT JOIN blogs b ON b.user_id = u.id  
LEFT JOIN messages m ON m.user_id = u.id  
GROUP BY u.id

It obviously doesn't work, because the second left join relates to blogs not users. Any suggestions?

score 3 · Accepted Answer · answered Oct 13 '11 at 15:09

First, if you only want the count value, you could do subselects:

select u.id, u.name, 
    (select count(b.id) from blogs where userid = u.id) as 'blogs',
    (select count(m.id) from messages where userid = u.id) as 'messages'
from 'users'

Note that this is just a plain sql example, I have no mysql db here to test it right now.

On the other hand, you could do a join, but you should use an outer join to include users without blogs but with messages. That would imply that you get several users multiple times, so a group by would be helpful.

@user993692 - That depends on the subquery. Modern databases are smart enough to optimize a subselect, and in this case you only need a key search, which is quite lightweight (assumed you don't have a quadrillion users). — z00l, Oct 13 '11 at 15:52

Johan · Answer 2 · 2011-10-13T15:16:53.080

If you use an aggregate function in a select, SQL will collapse all your rows into a single row.
In order to get more than 1 row out you must use a group by clause.
Then SQL will generate totals per user.

Fastest option

SELECT 
  u.id
  , (SELECT(COUNT(*) FROM blogs b WHERE b.user_id = u.id) as blogcount
  , (SELECT(COUNT(*) FROM messages m WHERE m.user_id = u.id) as messagecount
FROM users u

Why you code does not work

SELECT u.id, count(b.id), count(m.id) 
FROM users u   
LEFT JOIN blogs b ON b.user_id = u.id       <<-- 3 matches multiplies # of rows *3
LEFT JOIN messages m ON m.user_id = u.id    <<-- 5 matches multiplies # of rows *5
GROUP BY u.id

The count will be off, because you are counting duplicate items.

Simple fix, but will be slower than option 1
If you only count distinct id's, you will get the correct counts:

SELECT u.id, count(DISTNICT b.id), count(DISTINCT m.id) 
FROM users u   
LEFT JOIN blogs b ON b.user_id = u.id     
LEFT JOIN messages m ON m.user_id = u.id    
GROUP BY u.id

I got the `group by` already, just forgot to write it down as well. But the result is still wrong, would look like: `User | count(blogs) | count(messages) Jim | 0 | 0 Tom | 2 | 2 Tim | 0 | 0 Foo | 2 | 2` — user993692, Oct 13 '11 at 15:10

Left Join 2 tables on 1 table

2 Answers2