Re: yet another hierarchy model

From: --CELKO-- <71062.1056_at_compuserve.com>
Date: 15 Oct 2001 09:04:37 -0700
Message-ID: <c0d87ec0.0110150804.5bc67845@posting.google.com>

>> 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 - 11:04:37 CDT