January 23rd, 2003, 10:22 PM
select query with count
this may be simple but i haven't yet been able to figure it out...i have three tables invloved in this query...one for forums, one for the threads, and one for posts...the format is like this...
forumID | forumTitle
threadID | threadForum
postID | postThread
all the IDs are auto increasing integers. threadForum is a foreign key that links a certain thread to which forum it is part of and postThread is a foreign key that links it to a thread...a thread has a series of posts related to it. with the lowest postID being the first post in the thread obviously. now how do i return a recordset with the rows being each forums title, a count of how many threads there in the forum, and a count of how many posts there are in a forum? the main problem i'm getting is that i can't get an aggregate function like count() to go with a row for each forum...does this involve two subqueries somehow?? anyone got any tutorials that will help? rob
Delenda est Carthago
January 24th, 2003, 02:57 AM
What is the query you are trying to do?
SELECT forum.title, count(threads.threadID), count (posts.postID) FROM (posts INNER JOIN threads ON posts.postThread = threads.threadID) INNER JOIN forum ON threads.threadForum = forum.forumID GROUP BY posts.postID, threads.threadID
Could something like this help?
January 24th, 2003, 02:56 PM
where threadforum = f.forumid )
from posts inner join threads
on posts.posthread = threads.threadid
where threads.threadforum = f.forumidid )
from forum f