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

On Thursday 02 Oct 2003 09:13 in
<404a8308(dot)0310020013(dot)5294255(at)posting(dot)google(dot)com>, Chris
(mvppetlab(at)yahoo(dot)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(at)spamtrap(dot)ntlworld(dot)com (David W Noon)
Remove spam trap to reply via e-mail.
======================================================