Re: Extracting Parent Model from Nested Sets Model

71062.1056_at_compuserve.com (--CELKO--) wrote in message news:<c0d87ec0.0301211010.30b4a561_at_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;

 DECLARE @parent_left INTEGER;
 DECLARE @parent_right INTEGER;
 SET @parent_left = 7;
 SET @parent_right = 16;

 SET @next_brother = @parent_left+1;

> SELECT CASE WHEN lft >= @next_brother
> THEN rgt+1
> ELSE @next_brother END AS child,
> part AS parent
> FROM Frammis

   WHERE lft >= @parent_left AND rgt <= @parent_right    HAVING child = rgt+1

> ORDER BY lft;
> END;
Does that work? in SQL Server?

Regards,
Nuncanada Received on Tue Jan 21 2003 - 21:39:29 CST