Re: Celko: short book on the nested sets model for trees.
Date: 5 May 2002 08:39:13 -0700
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
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 - 17:39:13 CEST