Results 1 to 2 of 2
  1. #1
    Join Date
    Jun 2003
    Location
    Tacoma, WA
    Posts
    4

    Unanswered: Retrieving sub trees and levels in a nested set

    Hello,

    I have a nested set hierarchy set up that looks a lot like this.

    TABLE
    Id, lft, rgt
    A, 1, 14
    B, 2, 3
    C 4, 13
    D 5, 6
    E 7, 10
    F 8, 9
    G 11, 12

    I want to select specific nodes and return its parents like this.

    SELECT one.*
    FROM table AS one, table AS two
    WHERE two.lft BETWEEN one.lft AND one.rgt
    AND one.id IN (D, E, B)

    Returns something like:

    A, 1, 14
    B, 2, 3
    C, 3, 13
    D, 5, 6
    E, 7, 8

    But not F or G because they are not above D, E, or B.

    But I also want to know what level each of the nodes in the hierarchy is on. For example:

    A, 0
    B, 1
    C, 1
    D, 2
    E, 2

    Similar to what the following query returns only limited to the sub trees I select.

    SELECT count(two.id) AS level, one.id
    FROM table AS one, table AS two
    WHERE one.lft BETWEEN two.lft AND two.rgt
    GROUP BY one.id

    So, what I really want to do is combine these two queries so that I can select multiple sub trees and know what each of the levels of hierarchy are.

    Any help is greatly appreciated!
    -Rob

  2. #2
    Join Date
    Jun 2003
    Location
    Tacoma, WA
    Posts
    4

    Re: Retrieving sub trees and levels in a nested set

    This is what I did...it's not very elegant so if anyone has more suggestions please let me know.

    SELECT two.id, (SELECT count(*)
    FROM table
    WHERE lft <= two.lft
    AND rgt >= two.rgt) AS level
    FROM table AS one, table AS two
    WHERE one.lft BETWEEN two.lft and two.rgt
    AND one.id IN ('D', 'E', 'B')
    GROUP BY two.id, two.lft, two.rgt
    ORDER BY two.lft

    It takes a bit more time than I had desired but it works for now. I am still researching other options so please feel free to reply to this message.

    -Rob

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •