Re: Extracting Parent Model from Nested Sets Model

From: --CELKO-- <71062.1056_at_compuserve.com>
Date: 21 Jan 2003 10:10:03 -0800
Message-ID: <c0d87ec0.0301211010.30b4a561@posting.google.com>

The OUTER JOIN is the only answer I know other than a cursor. Let's see if we can get a translation into Standard SQL instead of that strange MySQL dialect.

CREATE TABLE Frammis
(part CHAR(2) NOT NULL
 lft INTEGER NOT NULL,
 rgt INTEGER NOT NULL);

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

I do not know MySQL, so I guessed at the translation to SQL Server:

BEGIN
DECLARE @next_brother INTEGER;
SET @next_brother = 8;
SELECT CASE WHEN lft >= @next_brother

            THEN rgt+1 
            ELSE @next_brother END AS child, 
        part AS parent

   FROM Frammis
  ORDER BY lft;
END; But this cannot return the right answser because the first column is numeric. What does your code do? For those of you who are lurking, here is the other solution:

SELECT B.part AS boss, P.part
  FROM Frammis AS P

       LEFT OUTER JOIN
       Frammis AS B
       ON B.lft
          = (SELECT MAX(lft)
               FROM Frammis AS S
              WHERE P.lft > S.lft
                AND P.lft < S.rgt);

Which gives us:
 boss part

---

 NULL	A 
 A 	B 
 B 	E 
 A 	C 
 C 	F 
 F 	I 
 F 	J 
 J 	M 
 J 	N 
 C 	F1
 C 	G 
 A 	D 
 D 	H 
 H 	K 
 H 	L

Received on Tue Jan 21 2003 - 12:10:03 CST