|
|
Posted by Erland Sommarskog on 03/25/06 01:14
Brian (dotnetdev@gmail.com) writes:
> However I would prefer to the childnode, and then get parents of that
> child recursively. Doing that would leave me with a result set that
> could add the top level menu items to and have all the data required.
> Any help is greatly appreciated.
I couldn't really understand how you wanted the output. However, judging
from the link you posted, you might be looking for something like:
declare @root int;
set @root = 6;
WITH Nav([NodeId],[ParentNodeId], [Text], [Level]) AS
(
SELECT n2.NodeId, n2.ParentNodeId, n2.Text, n2.[Level]
FROM [dbo].[NavigationNode] n1
JOIN NavigationNode n2 ON n1.ParentNodeId = n2.ParentNodeId
WHERE n1.NodeId = @root
UNION ALL
SELECT n2.NodeId, n2.ParentNodeId, n2.Text, n2.[Level]
FROM [dbo].[NavigationNode] n1
JOIN NavigationNode n2 ON n1.ParentNodeId = n2.ParentNodeId
INNER JOIN Nav n3 ON n3.[ParentNodeId] = n1.[NodeId]
)
SELECT DISTINCT *
FROM Nav
UNION ALL
SELECT NodeId, ParentNodeId, Text, [Level]
FROM NavigationNode
WHERE ParentNodeId IS NULL
Maybe not the most elegant, but the hour is late here...
--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
Navigation:
[Reply to this message]
|