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: 256

Filed under:
|
|
|

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

Related posts about sql

Related posts about mysql