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

From: --CELKO-- <>
Date: 5 May 2002 08:39:13 -0700
Message-ID: <>

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

       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 - 17:39:13 CEST

Original text of this message