Hello guys, first time poster. Judging by what I see here, I feel really, really dumb, so I feel bad for asking stupid questions.
My problem is somewhat lengthy.
Anyhow, I can't figure out how to structure this query for the life of me. First, some background info:
Posts table
-----------
postid [unique ID, of course]
forumid [this post belongs to this forum]
subject [self-explanatory]
body [self-explanatory]
threadRoot [If this post is the start of a new thread, then this will equal its own postid. If it is a reply in a thread, then this is equal to the first post of that thread's postid]
posterid [the id of the user who posted it]
datetime [self-explanatory]
Okay, this is what i have going on [forumid shall equal x for the sake of this example, it really doesn't matter]:
SELECT TOP 30 username, userid, posts.postid, posts.posterid, posts.subject, posts.threadroot, posts.datetime FROM posts INNER JOIN users ON users.userid=posts.posterid AND forumid=x WHERE threadroot=postid ORDER BY postid DESC
Now, how can I alter this so I get a count of posts per thread? I have managed [somehow] to create some wacky aggregate function-containing query, counting total posts per forum, but I cannot seem to apply what I did to this situation.
Speaking of, let me post up that utterly, utterly hideous query, in hopes that I could be taught a thing or two about making spiffy aggregate-function containing queries.
SELECT forums.*, count(postID) AS postcount FROM Forums,posts where forums.forumID=posts.forumID GROUP BY posts.forumID, Forums.forumPermission, Forums.forumDescription, forums.forumid, Forums.forumName
The deal with the massive GROUP BY clause is that it kept on stating that such-and-such field was not included in it and errored out, so I went ahead and stuck all of them in there. Out of spite. >.>
Your help is appreciated!
My problem is somewhat lengthy.
Anyhow, I can't figure out how to structure this query for the life of me. First, some background info:
Posts table
-----------
postid [unique ID, of course]
forumid [this post belongs to this forum]
subject [self-explanatory]
body [self-explanatory]
threadRoot [If this post is the start of a new thread, then this will equal its own postid. If it is a reply in a thread, then this is equal to the first post of that thread's postid]
posterid [the id of the user who posted it]
datetime [self-explanatory]
Okay, this is what i have going on [forumid shall equal x for the sake of this example, it really doesn't matter]:
SELECT TOP 30 username, userid, posts.postid, posts.posterid, posts.subject, posts.threadroot, posts.datetime FROM posts INNER JOIN users ON users.userid=posts.posterid AND forumid=x WHERE threadroot=postid ORDER BY postid DESC
Now, how can I alter this so I get a count of posts per thread? I have managed [somehow] to create some wacky aggregate function-containing query, counting total posts per forum, but I cannot seem to apply what I did to this situation.
Speaking of, let me post up that utterly, utterly hideous query, in hopes that I could be taught a thing or two about making spiffy aggregate-function containing queries.
SELECT forums.*, count(postID) AS postcount FROM Forums,posts where forums.forumID=posts.forumID GROUP BY posts.forumID, Forums.forumPermission, Forums.forumDescription, forums.forumid, Forums.forumName
The deal with the massive GROUP BY clause is that it kept on stating that such-and-such field was not included in it and errored out, so I went ahead and stuck all of them in there. Out of spite. >.>
Your help is appreciated!
