Left Join 2 tables on 1 table

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:

SE开发者_开发知识库LECT 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?

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.

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