Counting Comments

[douceur]

Alright... I'm working on a simple system to post messages and for people to comment on them. I have a table for posts and a table for comments. The posts table contains, among other things, a unique id for each post. The comments table contains a unique id for each comment along with a column for the post id it pertains to.

Is there a way that I can select all the posts and the number of comments for that post? Basically something like:

SELECT posts.*, count(comments.commentID) FROM posts as posts, comments as comments WHERE posts.postID = comments.postID

ROFL. I realize that's lame and isn't close to being correct syntax... I just have no idea how to do what I want to do, so I tried to write it so it would be easy to understand.

[LordShryku]

Try this

SELECT    posts.*, 
          count(comments.commentID) 
FROM      posts
LEFT JOIN comments 
ON        posts.postID = comments.postID

You want something to pull the posts even if there aren't comments on it, this should do that

[douceur]

Alright, awesome. Thanks for the quick reply! It must have been my lame attempt that got you on your way. Hehe, 😉

Haha, I like your signature, as well.