MySQL Forums
Forum List  »  Newbie

getting a count of records based on inner relationships...?
Posted by: Brian Culler
Date: May 12, 2005 03:17PM

I am making a PHP-MySQL forum. I have a table "forum" that holds all the posts.

Fields in forum: ID, user, title, body, threadID, datePosted.

All top level posts (ie, original posts for a thread) have a threadID=0. All other posts are comments, hence their threadID is set equal to the 'ID' of the original thread post.

I can get a list of the last 10 top level thread posts:
select * from forum where threadID=0 order by ID desc limit 0,10;

Also, I can get the # of comments for an individual thread (for instance, thread # 37):
select count(*) as Expr1 from forum where threadID=37;


How in the heck do I form those two statements into one statement? In my main forum view where I list the last 10 threads, I want returned in each result set the ID, title, user, and the # of comments posted in that thread.

Any ideas?

Options: ReplyQuote




Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.