Re: Website navigation hierarchy with SQL Server 2005
- From: Erland Sommarskog <esquel@xxxxxxxxxxxxx>
- Date: Fri, 24 Mar 2006 23:14:22 +0000 (UTC)
Brian (dotnetdev@xxxxxxxxx) 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@xxxxxxxxxxxxx
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
.
- References:
- Prev by Date: Re: ORDER BY in VIEW not working
- Next by Date: Re: ORDER BY in VIEW not working
- Previous by thread: Website navigation hierarchy with SQL Server 2005
- Next by thread: help with group by statement
- Index(es):
Relevant Pages
|
