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