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,
DanThis 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.|||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