LEFT JOIN 으로 다른 TABLE의 COUNT 구하기

 

SELECT post.id, post.title, user.id AS uid, username, COALESCE(x.cnt,0) AS comment_count
FROM `post`
LEFT JOIN post_user ON post.id = post_user.post_id
LEFT JOIN user ON user.id = post_user.user_id
LEFT OUTER JOIN (SELECT post_id, count(*) cnt FROM post_comments GROUP BY post_id) x ON post.id = x.post_id
ORDER BY post_date DESC