How to get count of another table in a left join
- by Sinan
I have multiple tables
post
id Name
1 post-name1
2 post-name2
user
id username
1 user1
2 user2
post_user
post_id user_id
1 1
2 1
post_comments
post_id comment_id
1 1
1 2
1 3
I am using a query like this:
SELECT post.id, post.title, user.id AS uid, username
FROM `post`
LEFT JOIN post_user ON post.id = post_user.post_id
LEFT JOIN user ON user.id = post_user.user_id
ORDER BY post_date DESC
It works as intended. However I would like the get the number of comments for each post too. So how can i modify the this query so I can get the count of comments.
Any ideas?
