Re: parent/child relationship in the same table.

In article <6768b874.0111152011.22f6087b_at_posting.google.com>, "Jason Diamond" <jason_at_injektilo.org> wrote:

> Hi.
>
>
> But what I really want is to get back each row with it's children
> immediately following it like this:
>
> 1, 0, 'one'
> 3, 1, 'child of one'
> 5, 3, 'child of child of one'
> 2, 0, 'two'
> 4, 0, 'four'
>
> I can't think of any way to do this with a simple order by
> clause--there's obviously no columns that are ordered in my desired
> results.
>
>
>
>
There are really only two orders in SQL - numeric and lexicographic (char, or nchar, varchar, varbinary, etc.). You're going to have to come up with a number or a character expression which reflects the order you wish.

A simple way is to materialize the paths for each element. For instance, with the above id's, you can construct a "path" column:

0

0.1
0.1.3
0.1.3.5
0.2
0.4

This is an example of lexicographic order. Please note that lexicographic order is the same as the order in which one would encounter the nodes in a depth-first search.

Paths are fairly easy to build. One way is to start with the root node and iteratively set each encountered node's path to its parent's path concatenated with the node id, eg

update table set path = '0.' where node_id = 0

update c set path = p.path + c.node_id
from table p, table c
where c.parent_id = p.node_id
and p.path is not null.
and c.path is null

Iterate the second expression until no more rows are updated (most db's let you check the "rows updated" after each statement). The number of iterations required is the number of levels in the hierarchy (i.e., not too many).

Or, if you have DB2, write a recursive expression which will construct the same thing in one statement. Received on Sun Nov 18 2001 - 09:00:38 CET