Re: parent/child relationship in the same table.

Hi.

> 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

I had exactly this same thought after asking my original question. It seems to suffer from two problems, though. The first is that I'd still like to maintain the original "numeric" ordering and to do that I would have to prefix each step in the path with zeroes which would make the paths pretty verbose which leads to my next problem: given enough descendants, I can eventually exceed the length limit on the path column.

> 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).

This is really interesting. I was thinking that I would just build the path when I do the first insert. Of course, I'd have to know the ID before that so couldn't use an IDENTITY column.

> Or, if you have DB2, write a recursive expression which will construct
> the same thing in one statement.

I'm using SQL Server but want to make sure that it's portable.

Thanks,
Jason. Received on Mon Nov 19 2001 - 17:52:55 CET