Re: Trees in SQL

>> Table relationship indicates which entity owns which and by what
percentage. For example, in the sample data above, LEC1 owns 80% of LEC2 and 80% of LEC3, LEC 2 owns 100% of LEC4 and 80% of LEC5,...and so on. Please note the split relationship in entity LEC7 (i.e., LEC 4 and LEC5 each own 50% in LEC7) which is causing some problems in the design of my databases/queries. <<

Get a copy of SQL FOR SMARTIES and look at the Parts explosion code for a Frammis. I have nodes which include the weight per unit and number of units of each type used to build a frammis. You can fairly easily propagate (wgt * tally) calculations up the tree, on level at a time once you have the leaf nodes (i.e. parts) in place. Thus is part C is made of two A's and four B's, and A's weigh 10 kilos and B's weigh 5 kilos, we have weight(C) = (2 * 10) + (4 * 5) = 40 kilos.

Since you have a lattice and not a tree, you will have to split the nodes to force this into a tree:

CREATE TABLE Tree
(node_id INTEGER NOT NULL -- can be repeated

         REFERENCES Nodes(node_id),
 lft INTEGER NOT NULL,
 rgt INTEGER NOT NULL,
 PRIMARY KEY (lft, rgt)); -- this is the key, not node_id

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

This should help you find the correct percentages.

Otherwise, hire me as a consultant for a few days ... Received on Wed Jul 03 2002 - 20:59:10 CEST