Nested Sets - get all immediate children of a node

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Nested Sets - get all immediate children of a node

Author

Topic

djpeanut
Starting Member

9 Posts

Posted - 2009-12-10 : 12:44:51

Help!

I have a Category tree stored using the nested sets model, with each node having a LeftIndex and a RightIndex, as well as a CategoryID and Title.

Given the CategoryID of a node, I want to retrieve all of its immediate children - i.e. the nodes which are one level deeper. These nodes may have their own children, but I don't want to return those, just the immediate children, ordered by LeftIndex.

I have a huge SELECT statement which I thought should work, but it's not.


SELECT     c.Title, c.CategoryID, (COUNT(p1.CategoryID) - (s.sDepth + 1)) AS Depth
FROM         
                      Category AS c CROSS JOIN
                      Category AS p1 CROSS JOIN
                      Category AS p2 CROSS JOIN
                      (SELECT     TOP (100) PERCENT c.Title, COUNT(p.CategoryID) - 1 AS sDepth
                       FROM          Category AS c CROSS JOIN
                                              Category AS p
                       WHERE      (c.LeftIndex BETWEEN p.LeftIndex AND p.RightIndex) AND (c.CategoryID = @CategoryID)
                       GROUP BY c.Title, c.LeftIndex
                       ORDER BY c.LeftIndex) AS s 
WHERE     (c.LeftIndex BETWEEN p1.LeftIndex AND p1.RightIndex) AND (c.LeftIndex BETWEEN p2.LeftIndex AND p2.RightIndex) AND s.Title = p2.Title
GROUP BY c.Title, c.CategoryID, c.LeftIndex, sDepth
HAVING sDepth = 1
ORDER BY c.LeftIndex

I'm having real trouble with this - can anyone help?

Thanks

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2009-12-10 : 13:11:35

Hi djpeanut

Using this as an example: http://en.wikipedia.org/wiki/Nested_set_model here's one way to select immediate children...

declare @t table (Node varchar(20), Lft int, Rgt int)
insert @t
          select 'Clothing', 1, 22
union all select 'Men', 2, 9
union all select 'Women', 10, 21
union all select 'Suits', 3, 8
union all select 'Slacks', 4, 5
union all select 'Jackets', 6, 7
union all select 'Dresses', 11, 16 --
union all select 'Skirts', 17, 18 --
union all select 'Blouses', 19, 20 --
union all select 'Evening Gowns', 12, 13
union all select 'Sun Dresses', 14, 15

--immediate children of 'Women'
; with t1 as (select b.* from @t a inner join @t b on a.Node = 'Women' and a.Lft < b.Lft and b.Lft < a.Rgt)
select * from t1 a where not exists (select * from t1 where Lft < a.Lft and a.Lft < Rgt)
--/

Ryan Randall - Yak of all trades
Solutions are easy. Understanding the problem, now, that's the hard part.

djpeanut
Starting Member

9 Posts

Posted - 2009-12-10 : 13:39:58

You rule. Thank you.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources