Re: Extracting Parent Model from Nested Sets Model

From: Flavio Botelho <nuncanada_at_ig.com.br>
Date: 21 Jan 2003 10:23:17 -0800
Message-ID: <de0df942.0301211023.5cbe9477_at_posting.google.com>

When i foresaw it would work i forgot about completing the query to get what was actually wanted... So here it is for mysql:

SELECT _at_parent_left = 33; //Change these to fit your needs SELECT _at_parent_right = 102;

SELECT _at_next_brother:=_at_parent_left;

SELECT _at_next_brother:=

   CASE WHEN left >= _at_next_brother

     THEN right+1 
     ELSE _at_next_brother 

   END AS next_brother, name, right
   FROM categories
   WHERE left >= _at_parent_left AND right <= @parent_right    HAVING next_brother = right+1
   ORDER BY left;

Nuncanada

nuncanada_at_ig.com.br (Flavio Botelho) wrote in message news:<de0df942.0301202104.9bb8e47_at_posting.google.com>...
> As i had seen an outer join as the only suggestion out there i
> wondered if there weren´t a faster query for tables with a lot of
> nested sets.
>
> This is for mysql, but seems to be translatable to other dbs(?):
>
> SELECT _at_next_brother:=33; //<- left + 1 for a specific parent
> SELECT _at_next_brother:=
> CASE WHEN left >= _at_next_brother
> THEN right+1
> ELSE _at_next_brother
> END, name
> FROM categories
> ORDER BY left;
>
> Nuncanada
Received on Tue Jan 21 2003 - 19:23:17 CET