Results 1 to 3 of 3
  1. #1
    Join Date
    May 2003
    Posts
    2

    Unanswered: Nested Set Model: Excluding Branches...

    I'm using the nested set model on to manage a sitemap index in my CMS application. The one issue I'm running in to is how to dynamically remove branches from the tree. I can get specific nodes to be removed from the querying, but their children really need removed as well. Currently the only way I've found to do this, is to use a cursor and go copy the "good" nodes to a temp table. Has anyone found a way to do this?

    Here's an example:
    - Item 1
      - Child 1
      - Child 2
          - Sub 1
          - Sub 2
          - Sub 3
       - Child 3
    - Item 2
    - Item 3
      - Child 1
          - Sub 1
      - Child 2
    - Item 4

    In the above tree, let's say I need to temporarily remove the "Item 3" node and it's children. I can remove "Item 3" easily enough, but the children items remain. Nothing I've come up with will exclude *all* the children (regarding of the depth of the nodes.)

    Anyone have any ideas?

    Thanks,
    Dan

  2. #2
    Join Date
    Jan 2003
    Location
    Leamington Spa, UK
    Posts
    17
    This should work:

    SELECT *
    FROM ns
    WHERE ns_id NOT IN (
    SELECT n1.ns_id
    FROM ns n1, ns n2
    WHERE n1.ns_left >= n2.ns_left
    AND n1.ns_right <= n2.ns_right
    AND n2.ns_id = 10 -- ie. primary key of 'item 3'
    )

    First, you need to get a list of primary keys which represent the rows you don't want - ie. item3 and all of its children. You then use a subquery to remove these from the result set.

    macka.

  3. #3
    Join Date
    May 2003
    Posts
    2
    Originally posted by macka
    This should work:

    SELECT *
    FROM ns
    WHERE ns_id NOT IN (
    SELECT n1.ns_id
    FROM ns n1, ns n2
    WHERE n1.ns_left >= n2.ns_left
    AND n1.ns_right <= n2.ns_right
    AND n2.ns_id = 10 -- ie. primary key of 'item 3'
    )

    First, you need to get a list of primary keys which represent the rows you don't want - ie. item3 and all of its children. You then use a subquery to remove these from the result set.

    macka.
    Perfect! I played around w/combinations that were close yesterday, but I was seriously overly complicating the process.

    Thanks a million!
    -Dan

Posting Permissions

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