Re: Tree (forest) design

>> Can you prove that your steps actually work by producing a "Nearest
Common Ancestor Report" equivalent to that shown at www.xdb1.com/Example/Ex076.asp starting from the same normalized, null-less data? Can you implement your steps using MS Access or SQL-Server so that we can verify it? <<

Oh, oh, oh!! Call on me, sir! I know the answer.

Let me steal code from one of my books, the parts explosion of a Frammis ina nested sets model:

CREATE TABLE Frammis
(part CHAR(2) PRIMARY KEY,
 lft INTEGER NOT NULL UNIQUE
 CONSTRAINT valid_lft CHECK (lft > 0),
 rgt INTEGER NOT NULL UNIQUE
 CONSTRAINT valid_rgt CHECK (rgt > 1),
 CONSTRAINT valid_range_pair CHECK (lft < rgt));

DELETE FROM Frammis;

INSERT INTO Frammis VALUES ('A', 1, 28);
INSERT INTO Frammis VALUES ('B', 2, 5);
INSERT INTO Frammis VALUES ('C', 6, 19);
INSERT INTO Frammis VALUES ('D', 20, 27);
INSERT INTO Frammis VALUES ('E', 3, 4);
INSERT INTO Frammis VALUES ('F', 7, 16);
INSERT INTO Frammis VALUES ('G', 17, 18);
INSERT INTO Frammis VALUES ('H', 21, 26);
INSERT INTO Frammis VALUES ('I', 8, 9);
INSERT INTO Frammis VALUES ('J', 10, 15);
INSERT INTO Frammis VALUES ('K', 22, 23);
INSERT INTO Frammis VALUES ('L', 24, 25);
INSERT INTO Frammis VALUES ('M', 11, 12);
INSERT INTO Frammis VALUES ('N', 13, 14);

• This gives all superiors and orders them with the spread SELECT DISTINCT :part_1 AS part_1, :part_2 AS part_2, F1.part AS common_ancestor, (f1.rgt-f1.lft) AS spread FROM Frammis AS F1 WHERE (SELECT lft FROM Frammis WHERE part = :part_1) BETWEEN F1.lft AND F1.rgt AND (SELECT lft FROM Frammis WHERE part = :part_2) BETWEEN F1.lft AND F1.rgt;

The nearest superior has the lowest spread value. That can done with a derived table or a WITH clause.

SELECT part_1, part_2, common_ancestor, spread   FROM (<<above>>)

       AS X1(part_1, part_2, common_ancestor, spread  WHERE X1.spread

• (SELECT MIN(spread) FROM (<<above>>) AS X2(part_1, part_2, common_ancestor, spread);

 Or

  WITH (<<above>>)
    AS X(part_1, part_2, common_ancestor, spread) SELECT part_1, part_2, common_ancestor, spread   FROM X
 WHERE spread = (SELECT MIN(spread) FROM X); Received on Thu Mar 04 2004 - 23:30:56 CET