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 _at_next_brother INTEGER;
 DECLARE _at_parent_left INTEGER;
 DECLARE _at_parent_right INTEGER;
 SET _at_parent_left = 7;
 SET _at_parent_right = 16;

 SET _at_next_brother = @parent_left+1;

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

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

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

Regards,
Nuncanada Received on Wed Jan 22 2003 - 04:39:29 CET