Re: Can SQL return a threaded-comment-view result set?

On Thursday 02 Oct 2003 09:13 in
<404a8308.0310020013.5294255@posting.google.com>, Chris
(mvppetlab@yahoo.com) wrote:

> Suppose you want to use an RDBMS to store messages for a threaded
> message forum like usenet and then display the messages. A toy table
> definition (that I've tried to make standards compliant) might look
> like:
>
> create table messages (
>        message_id integer,
>        in_reply_to integer,
>        created date,
>        author varchar(20),
>        title varchar(30),
>        message varchar(256),
>        primary key (message_id)
> );
>
>
> The in_reply_to field, if not null, means that the message is a reply
> to the message with the message_id it has stored. Suppose now that we
> populate the database with a 5 message discussion.

You will need a second table, called a path enumeration table. Joe Celko
wrote up this technique in his book "SQL For Smarties".

I think I can dig up some sample SQL for you, as I used this technique
several times a few years ago -- although on DB2 rather than PostrgeSQL.
Since the SQL can be a bit intricate, I have set follow-ups to
comp.databases.postgresql.sql, as it would be more on-topic there.

However, I recommend Joe Celko's book, as it explains the technique as well
as demonstrates it.
--
Regards,

Dave  [RLU#314465]
======================================================
dwnoon@spamtrap.ntlworld.com (David W Noon)
Remove spam trap to reply via e-mail.
======================================================