Re: yet another hierarchy model

>> I think this approach is somewhat similar to Celko's nested set: <<

Thye are both traversal algorithms, mine is a pre-order traversal, and this is an enorder tarversal with a diferent numbering algorithm.

>> I think best would be a combination of both: left, right, level <<

I can generate the level number with a subquery that counts the superiors of a node -- i.e. the number of rows which contain this node:

 CREATE VIEW CombinedTree (node, lft, rgt, level)  AS SELECT node, lft, rgt,

            (SELECT COUNT(*)
               FROM CelkoTree AS C2
              WHERE C1.lft BETWEEN C2.lft AND C2.rgt) 
      FROM CelkoTree AS C1;

Having the level lets you find the immediate subordinates by just looking for (level+1), while I have to use subqueries with self-joins. Received on Mon Oct 15 2001 - 18:04:37 CEST