Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> comp.databases.theory -> Re: Celko: short book on the nested sets model for trees.

Re: Celko: short book on the nested sets model for trees.

From: --CELKO-- <71062.1056_at_compuserve.com>
Date: 5 May 2002 08:39:13 -0700
Message-ID: <c0d87ec0.0205050739.6c8759da@posting.google.com>


>> I stumbled onto your nested set articles and I want to first thank
you. <<

if you bought my book, then my royalty payments are thanks enough <g>.

>> ... I solved the problem by creating a Hierarchy table and linking
that with the nested set table. Of course that led to a separate node list table, but that was easy enough. <<

I will argue that normalization requires the separation. A table is a set; a set is made up of "the same kind of things"; the tree structure is a relationship and the nodes are entities; ergo, they should not be in the same table.

>> I'm having a problem with coming up with query that results in only
the
immediate children of a given node. Could you offer some insight? <<

To convert a nested sets model into an adjacency list model:

SELECT B.emp AS boss, P.emp
  FROM OrgChart AS P

       LEFT OUTER JOIN
       OrgChart AS B
       ON B.lft
          = (SELECT MAX(lft)
               FROM OrgChart AS S
              WHERE P.lft > S.lft
                AND P.lft < S.rgt);
Received on Sun May 05 2002 - 10:39:13 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US