Nested set show leaves of parent

Hello,

I have the following code which will show all bottom level leaf nodes of the hierachy:

SELECT name
FROM tree
WHERE rgt = lft + 1;

I'd like to be able to filter results by a node. For example in a tree such as:

Products

ReleaseProduct

Release1

Release build 1

Release build 2

Release 2

Release 2 build 1

Release 2 build 2

Build Product

Build 1

Build 2

If Build 2 is chosen (any node with no children) I'd like to just show the Buuild 2, if ReleaseProduct is chosen Release build 1, Release build 2, Release 2 build 1 and Release 2 build 2 will be shown and if BuildProduct is chosen I'd like to display Build 1, Build 2.

I understand the prinicipals but my SQL is quite lacking anything further than the select, where statements. If anyone could please lend me a little advice on how to go about this I would be very grateful!

Thanks :)

Hello,

Can you post the schema of the table in question and what version of SQL Server you are using?

If 2005, a recursive CTE sounds like it may suit, otherwise a more "creative" solution may apply. let us know the specifics and I'm sure we can help out.

Cheers,

Rob

|||Thank's for the quick reply!

The schema is as follows:

CREATE TABLE site_category(
site_id INT IDENTITY(1,1) PRIMARY KEY,
name VARCHAR(20) NOT NULL,
lft INT NOT NULL,
rgt INT NOT NULL
);

So a site may be a root, parent or child depending on the left and right values of the nodes in the hierachy. I'm using 2005 Express.

Thanks for the help!|||

Hello,

I don't know what lft or rgt is, but I'm going to assume that they contain the site_id of the parent node. So, to simplify this, let's call it ParentSiteID:

with Sites(SiteName, site_id, ParentID, NestLevel)
AS
(
SELECT [name], site_id, parentSiteID, 0
FROM site_category
WHERE [name] = 'Site123'
UNION ALL
SELECT sc.[Name], sc.Site_ID, s.Site_ID,(NestLevel + 1)
FROM Sites s
JOIN site_category sc ON s.Site_ID = sc.ParentSiteID
)
SELECT *
FROM Sites

The above example will return "Site123" and all child nodes therein (including any nested relationships). The NestLevel column indicates how deep the nesting level is. You'll need to adjust this to cater for your lft/rgt columns...

Cheers,

Rob

|||

The lft and rgt fields store values used to determine the level in the hierachy. The example from the MySQL site I am using as a guide is:

http://dev.mysql.com/tech-resources/articles/hierarchical-data.html

Following this I have got to the heading 'Finding the Depth of the Nodes' which produces the results I am after.

Where I'm having trouble is the heading 'Find the Immediate Subordinates of a Node' which is exactly what I need and is explained with code but I just can't figure it out! I feel there may be some subtle differences in the SQL used in this MySQL example and the TSQL SQL Server is expecting. Not to mention my SQL knowledge isn't great at this point!

I havn't tried your example but feel this post may offer a better explanation as (I may be wrong) your example looks like it assumes I am using an Adjacency List Model.

I appreciate your time! :)

|||

Hello,

OK, I understand what you're trying to do:

SELECT node.name, (COUNT(parent.name) - (sub_tree.depth + 1)) AS depth
FROM nested_category AS node,
nested_category AS parent,
nested_category AS sub_parent,
(
SELECT TOP 100 node.name, (COUNT(parent.name) - 1) AS depth
FROM nested_category AS node,
nested_category AS parent
WHERE node.lft BETWEEN parent.lft AND parent.rgt
AND node.name = 'PORTABLE ELECTRONICS'
GROUP BY node.name, node.lft
ORDER BY node.lft
)AS sub_tree
WHERE node.lft BETWEEN parent.lft AND parent.rgt
AND node.lft BETWEEN sub_parent.lft AND sub_parent.rgt
AND sub_parent.name = sub_tree.name
GROUP BY node.name, depth, node.lft
HAVING depth <= 1
ORDER BY node.lft;

Does that do what you want?

Cheers,

Rob

|||

That works exactly how I want!

Is the TOP keyword and value an approximation of the rows to be returned to be returned, as the complete result set is not loaded into memory?

Thanks :)

|||

Actually, the only reason to use TOP in the sub query is because without it, you cannot use an order by. So you could actually remove it and the corresponding order by:

SELECT node.name, (COUNT(parent.name) - (sub_tree.depth + 1)) AS depth
FROM nested_category AS node,
nested_category AS parent,
nested_category AS sub_parent,
(
SELECT node.name, (COUNT(parent.name) - 1) AS depth
FROM nested_category AS node,
nested_category AS parent
WHERE node.lft BETWEEN parent.lft AND parent.rgt
AND node.name = 'PORTABLE ELECTRONICS'
GROUP BY node.name, node.lft
)AS sub_tree
WHERE node.lft BETWEEN parent.lft AND parent.rgt
AND node.lft BETWEEN sub_parent.lft AND sub_parent.rgt
AND sub_parent.name = sub_tree.name
GROUP BY node.name, depth, node.lft
HAVING depth <= 1
ORDER BY node.lft;

Cheers,

Rob

|||Oh I see, Thanks again!sql