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

nested set model

Hi,

I am storing some hierarchical information using the nested set model. I would like to transform the data into XML to bind them to a TreeView object. Did someone do something similar before. Any input would be very much appreciated. Many thanks.

Christian

SQL Server 2000 or 2005?

Also you'll need to post some DDL to get any sort of answer

|||

-- An example for SQL Server 2005 only, may be helpful

CREATE TABLE MyTable(Name VARCHAR(10),lft INT,rgt INT)

INSERT INTO MyTable(Name,lft,rgt)
SELECT 'Top' , 1 , 12 UNION ALL
SELECT 'Level1A' ,2 , 3 UNION ALL
SELECT 'Level1B' ,4 , 11 UNION ALL
SELECT 'Level2A' ,5 , 6 UNION ALL
SELECT 'Level2B' ,7 , 8 UNION ALL
SELECT 'Level2C' ,9 , 10

GO
CREATE VIEW Adjacency
-- Converts the nested set to an adjacency list
AS
SELECT P.Name AS ParentName,
N.Name
FROM MyTable AS N
LEFT OUTER JOIN MyTable AS P ON P.lft = (SELECT MAX(S.lft)
FROM MyTable AS S
WHERE N.lft > S.lft
AND N.lft < S.rgt)
GO
CREATE FUNCTION dbo.SubTree(@.Name VARCHAR(10))
RETURNS XML
WITH RETURNS NULL ON NULL INPUT
BEGIN RETURN
(SELECT Name as "@.Name",
dbo.SubTree(Name)
FROM Adjacency
WHERE ParentName=@.Name
ORDER BY Name
FOR XML PATH('Node'),TYPE)
END

GO

SELECT Name as "@.Name",
dbo.SubTree(Name)
FROM Adjacency
WHERE ParentName IS NULL
ORDER BY Name
FOR XML PATH('Node'),ROOT('Nodes'),TYPE

|||

Thanks Mark. This helped. I got it all working fine.

Chris

nested set model

Hi,

I am storing some hierarchical information using the nested set model. I would like to transform the data into XML to bind them to a TreeView object. Did someone do something similar before. Any input would be very much appreciated. Many thanks.

Christian

SQL Server 2000 or 2005?

Also you'll need to post some DDL to get any sort of answer

|||

-- An example for SQL Server 2005 only, may be helpful

CREATE TABLE MyTable(Name VARCHAR(10),lft INT,rgt INT)

INSERT INTO MyTable(Name,lft,rgt)
SELECT 'Top' , 1 , 12 UNION ALL
SELECT 'Level1A' ,2 , 3 UNION ALL
SELECT 'Level1B' ,4 , 11 UNION ALL
SELECT 'Level2A' ,5 , 6 UNION ALL
SELECT 'Level2B' ,7 , 8 UNION ALL
SELECT 'Level2C' ,9 , 10

GO
CREATE VIEW Adjacency
-- Converts the nested set to an adjacency list
AS
SELECT P.Name AS ParentName,
N.Name
FROM MyTable AS N
LEFT OUTER JOIN MyTable AS P ON P.lft = (SELECT MAX(S.lft)
FROM MyTable AS S
WHERE N.lft > S.lft
AND N.lft < S.rgt)
GO
CREATE FUNCTION dbo.SubTree(@.Name VARCHAR(10))
RETURNS XML
WITH RETURNS NULL ON NULL INPUT
BEGIN RETURN
(SELECT Name as "@.Name",
dbo.SubTree(Name)
FROM Adjacency
WHERE ParentName=@.Name
ORDER BY Name
FOR XML PATH('Node'),TYPE)
END

GO

SELECT Name as "@.Name",
dbo.SubTree(Name)
FROM Adjacency
WHERE ParentName IS NULL
ORDER BY Name
FOR XML PATH('Node'),ROOT('Nodes'),TYPE

|||

Thanks Mark. This helped. I got it all working fine.

Chris

Nested set Model

Hello

I am trying to implement Joe Celko's Nested set model to create a treemenu. I read the article at http://www.dbmsmag.com/9604d06.html, and I am trying to delete a single node (have't yet tried to delete a subtree). I use the procedure from the article, and it seems to fill in the gap ok, but the problem is that the ancestors are not updated. I think I read in a post somewhere at the forum, by Mr. Celko, that the ancestors need not be updated, but he also states in the article that the root node's right is always n*2. Do I have to update the ancestors, or can I leave them alone? Does anyone have some cut & paste code they can provide to help me out?

Thanks in advance.

--Shai`tan--Celko's description of the nested set model looks pretty cool, and is probably more efficient than the adjacency model in many cases, but unless you are dealing with absolutely gigantic tables the adjacency method is more than satisfactory for storing tree structures. It is certainly easier to visualize, which means it is easier to code for and easier to debug, and these advantages cannot be lightly dismissed.

I have personally never seen the nested set model implemented.

blindman

negative output from neural networks

I am getting negative predictions (continuous) from a neural network model that has been trained on data that only contains positive values or zeros (no nulls).

Is there a setting that can limit the lower end of the output range to zero?

Hello

There is no algorithm setting that can limit the outputs range. However, you could use a different query construct to get this result. Assuming that your predictable column is labeled [Petal Length], your query could look like:

SELECT VBA FROM [Model] PREDICTION JOIN ...

The [IIF] function is part of a set of built-in custom functions and it is defined as

IIF( boolean_condition, true_value, false_value)

Hope this helps