Mysql – neat way to build a user posts/likes/comments query

MySQLsubquery

Say we have table users and three tables posts, comments, likes, which are many-to-one to users. I want to build some report query for all users, even if they have 0 posts, comments or likes.
Is there a way of doing this more elegant, than 

select 
  users.id, 
  users.name, 
  ifnull(u1.post_score,0), 
  ifnull(u1.comments_score,0), 
  ifnull(u1.likes_score,0)
from users left join (
select id,sum(post_score),sum(comments_score),sum(likes_score) from
(
(select 
  user.id,
  posts.score as post_score,
  0 as comments_score,
  0 as likes_score
from users inner join posts on posts.user_id = users.id)
union all
(select 
  user.id,
  0 as post_score,
  comments.score as comments_score,
  0 as likes_score
from users inner join comments on comments.user_id = users.id)
union all
(select 
  user.id,
  0 as post_score,
  0 as comments_score,
  1 as likes_score
from users inner join likes on likes.user_id = users.id)
) t1
group by id ) u1
on u1.id = users.id

? Thank you.
P.S. MySQL

Best Answer

SELECT u.id,
       u.name,
       COALESCE(p.cnt, 0) posts, 
       COALESCE(c.cnt, 0) comments, 
       COALESCE(l.cnt, 0) likes
FROM users u
LEFT JOIN ( SELECT user_id, COUNT(*) cnt
            FROM posts 
            GROUP BY user_id ) p ON  u.id = p.user_id
LEFT JOIN ( SELECT user_id, COUNT(*) cnt
            FROM comments
            GROUP BY user_id ) c ON  u.id = c.user_id
LEFT JOIN ( SELECT user_id, COUNT(*) cnt
            FROM likes
            GROUP BY user_id ) l ON  u.id = l.user_id
Related Question