Hi,
Originally postgres had a "recursive select" to handle cases like this.
Some syntax like...
retrieve* (notice the "*") which meant keep executing until you can't
anymore, and using an
appropriate where clause it would decend tree-like structures.
This feature disappeared somewhere along the way. There is I think a
similar concept in SQL
or SQL-99 which needs to be (re)implemented sometime, but I don't think
there's an easy way
right now. There was some talk on the hackers list recently about how to
implement parent
child comments in discussion forums, but I'm not sure if a nice solution
came along. Some
people seemed confident that there was a way.
database@gurubase.com wrote:
>
> Dear all,
>
> I would like to define threads in message system for replies to message but if
> I define too many level, I am afraid I have problem in the select...
>
> Say, I have define 3 levels:
>
> 1
> / \
> 2 3
> /\ \
> 4 5 6
>
> It means message 2 is a reply to 1.
> 4 is a further follow-up of 2...etc
>
> In table format, I would present it with
>
> Table A
> ParentId ChildId
> 1 2
> 1 3
> 2 4
> 2 5
> 3 6
>
> I think I can issue the command to join table A 3 times to give the following
> result
>
> 1st 2nd 3rd
> Row1 1 2 4
> Row2 1 2 5
> Row3 1 3 6
>
> But can I show
> a. which level each node belongs
> b. no. of child nodes it has
> altogether in one "select" sql
>
> and if the level exceeds 3, how can I do it?
>
> Many thanks.....
>
> Best regards,
> Boris