SQL: NestedSet Tree, getAncestors() with level

duff_beer · #1 (**permalink**) 07-22-04, 17:52

Hi all,

I have a tree structure stored in the db with the "Nested Set" model described
by Joe Celko.

The table is like this one:

Code:

=========================
| ID | l | r | name     |
=========================
| 1  | 1 | 8 | root     |
-------------------------
| 2  | 2 | 3 | 1stChild |
-------------------------
| 3  | 4 | 7 | 2ndChild |
-------------------------
| 4  | 5 | 6 | nephew   |
-------------------------

where l=leftId, r=rightId

the table describes the following tree structure:

Code:

          root
         /    \
   1stChild  2ndChild
                |
              nephew

I want to fetch the ancestors of a certain node.
This is the query:

Code:

  SELECT T1.ID, T1.name
    FROM mytable AS T1, mytable AS T2
   WHERE T1.l BETWEEN T2.l AND T2.r
     AND T1.ID=?

If I want to get the level (aka depth) of a certain node, I can use this query:

Code:

  SELECT COUNT(T2.ID)
    FROM mytable AS T1, mytable AS T2
   WHERE T1.l BETWEEN T2.l AND T2.r
     AND T1.ID=?
GROUP BY T1.ID

Now, how can I combine these 2 queries into one, without using subselects, views, or stored procedures?

This query will do the trick, but it will fetch all the nodes of the tree, not only the ancestors of a certain node:

Code:

    SELECT T1.ID, T1.name, COUNT(T2.ID)
      FROM mytable AS T1
INNER JOIN mytable AS T2
        ON T1.l BETWEEN T2.l AND T2.r
  GROUP BY T1.ID

To clarify, this is the query that returns the desired result, but I wish not to resort to 3 table joins...

Code:

  SELECT T1.ID, T1.name, COUNT(T3.ID) as level
    FROM mytable AS T1, mytable AS T2, mytable AS T3
   WHERE (T2.l BETWEEN T1.l AND T1.r)
     AND (T1.l BETWEEN T3.l AND T3.r)
     AND (T2.ID = ?)
GROUP BY T1.ID

is anyone able to simplify the last query, and make it less memory-intensive?

TIA

Internet Services

Web Development

Digital Marketing

Consumer Tech