Query For Forum Threads

Hi,
I am currently working on a forum application with Access

I need to create an Sql query that will return the originial postings and all related replies. For example:
Original Posting
->Reply to Original Posting
->Reply to Reply to Original Posting
->Reply to Reply to Reply to Original Posting
->2nd Reply to Original Posting

Table(Message)
--------
Messge_ID
Message_Postby
Message_Title
Message_Body
Message_InReplyTo
Message_Parent
Message_Date
Message_Replies - No of replies to this topic

How would I query for all related messages in one query? I am using 2 queries now, one to get the Original Posting and another to get the replies.

For the number of replies, this field is updated everytime there is a reply to this thread. Is there a way to use aggregate function to count the number of replies for to each message?

Hope it's understandable.

With access you have sub-selects. If the table is somewhat `normalized` you may be able to retrieve a unique message id and select all postings related to that message ID.

hmmm.. the ease of ur query is all dependent on how u set up ur relationship..

how does ur message ID field work..
does it go like this

2
2.1
2.2
2.2.1
2.2.2
2.2.3
2.3
2.4
??

sub-select as in sub queries? How do I do that?

The Message_ID is just a Autonumber generated by the database in form of
1
2
2

select somecolum
from table1
where table1Key in (select keycolumn from table2)

I think that will work if the replies are tied to one parent message. But in my case, replies got be tied to a reply to the parent message.

You should then be able to `daisy-chain` your way to the originating message. How are your message ids releated?

Message_Parent is the Message_ID of each original post. If the Message is a original post, Message_Parent will be 0. Message_InReplyTo is the Message_ID of any post.

What do you mean by 'daisy-chain'? In the SQL or script?

Does the original post have a message id? What is it? How is the reply message ID structured? And a reply to a reply?
Something like
Original: 1
Reply: 1.1
Reply to Reply: 1.1.1
Second reply: 1.2
Reply to second reply 1.2.1
and so on
would allow you retrieve all messages that derived from original post 1.

No the Message_ID is not structured in that way. It is just a plain number.
But I got when you meant.
Is that the only way to query for this kind of forum threading?

Nothing is absolute. But being structred that way would increase your flexibility. Based on what you have displayed I do not see an easy correlation between an originating post and a fifth generation reply.

message replies can be joined to the posts they are in reply to, by using a self-join

what you're asking for, amber, is recursion, and as far as i know, only oracle database directly supports that, using CONNECT BY syntax or something

if you're sure you never go deeper than 5 levels, you can use a six-table self join -- a bit awkward but it works

anyhow, linking a reply to its parent post using Message_Parent is an example of the adjacency model

do a google search for "celko nested set model" for another approach

here's a third way: http://www.sitepoint.com/article/1105

rudy

I am using Access and dumping all the sql statements to the database. So it is not possible to have a 6 table self-join.
Thanks for the enlightment on the Hierarchical data.

i do six-table joins in access all the time

i know it's possible

How did you do that?!
I can't make sense of a self-join. Now that's another thing to read up on.