Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2001-01-18 : 17:56:12
|
Robsa writes "Now Im not sure whether this is possible or not, but I am trying to get a count of a field in a table where I need to reference the autonumber field of the same table.
What I am basically trying to do is this:
I am writing a forum using ACCESS and the actual table that stores the threads works as such:
Each new thread is created with a new autoID field and the same value is put in a field called messagethread.
Each subsequent post to that thread is referenced by the second field, messagethread, to link it with the original thread.
So, the first post will have a autoID of 1 and a messagethreadID of 1. If the second post refers to the first thread then it will have and autoID of 2 and a messagethread of 1 (the original post).
If the 3rd post is a new thread then it will have an autoID of 3 and a messagethread of 3 and so on.
So, by reading a count of the messagethread field you can count how many posts there are to any given thread. What I dont know how to do is to get a single SQL query that will list each thread, and the number of posts to that thread
e.g. message 1, 3 threads, message 5, 2 threads, message 12, 18 threads and so on.....
is it possible to write a single SQL statement that can do this?" |
|
|
|
|
|
getting a COUNT cross-referencing a field in the same table