Forum Reply Count SQL

[JimCloud]

I have an Access database table for a user forum I creating with ASP. I have
two ID fields for each message:

"msg_ID" as the primary key for each message

and "msg_parentID" to reference which message is being replied to.

The problem is this: I can't figure out the query to run so that I can include
a "# of Replies" column in the message list on the page. Will this be easier to
acheive by doing a query in Access and referencing that, or writing the whole
query code in the page itself (or perhaps within the repeated region of the
message list)?

Any help would be very much appreciated. Thanks.

[Michael Fesser]

.oO(JimCloud)

>I have an Access database table for a user forum I creating with ASP. I have
>two ID fields for each message:
>
> "msg_ID" as the primary key for each message
>
> and "msg_parentID" to reference which message is being replied to.
>
> The problem is this: I can't figure out the query to run so that I can include
>a "# of Replies" column in the message list on the page.

The easiest way to solve that is to add a third ID field to each message
containing the thread ID, i.e. the ID of the first message in a thread.
Then all you have to do is to get the first message, grab its ID and
count how many other messages belong to that thread.

But if you want to show the number of replies for every single message
then it will become a bit more complicated ...

Micha

[JimCloud]

If I do add that third db field, what would the sql be to count the messages for each thread?

[Julian Roberts]

Maybe something like

select *,
(
select count(*) from messages b
where b.parentid=a.msgid
) as messagescount
from messages a

--
Jules
[url]http://www.charon.co.uk/charoncart[/url]
Charon Cart 3
Shopping Cart Extension for Dreamweaver MX/MX 2004

[JimCloud]

Figured it out. Thanks anyway!