How to get count of another table in a left join
Posted
by Sinan
on Stack Overflow
See other posts from Stack Overflow
or by Sinan
Published on 2010-06-02T16:39:12Z
Indexed on
2010/06/02
16:43 UTC
Read the original article
Hit count: 251
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?
© Stack Overflow or respective owner