multiple select statements!

[andrew_biggart]

Ok i am trying to create a forum, and i have got to the stage of the most popular posts! The way i have it set up is i have the original posts in one table and then the comments in another table! The posts have a primary key of post_id and the comments have a primary key of comment_id but it also has a column called post id to link the 2 tables together. But what i want to do is count which posts have the most comments from the comment table, and then when i have those say 5 results select the 5 posts from the post table by their post id! I cant gat my head around how i would go about starting this so any help will be appreciated thanks! Hopefully i have it explained what i want to do ok.

[kickstart]

Hi

 

Try this.

 

SELECT a.post_id, COUNT(b.comment_id)
FROM posts a
LEFT OUTER JOIN comments b
ON a.post_id = b.post_id
GROUP BY a.post_id

 

All the best

 

Keith

[andrew_biggart]

Sorry to be annoying keith but could you explain this to me abit more as i dont know whee to start please!

[Brian W]

Do you have phpMyAdmin? if so, through this into a query (slight mod of kickstart's, no guarantee it will work)

SELECT a.post_id, COUNT(b.comment_id) as `count`

FROM posts a

LEFT OUTER JOIN comments b

ON a.post_id = b.post_id

ORDER BY `count`

GROUP BY a.post_id

"LEFT OUTER JOIN" means you are linking the table with whatever is on the left side of the "ON" statement's "=" having to be in existence (if your post is deleted, it won't try counting from the comments for that post). The on statement is how you specify what fields need to be equal. in this case, the post id fields in both tables. "COUNT()" counts how many results were returned from the table comments. If you notice that the tables are names then have "a" or "b" after them, that is called an "alias" and it just simply makes it easier/shorter to write.

 

Help?