Website navigation hierarchy with SQL Server 2005

Hi

I'm trying to convert some verbose SQL Server 2000 T-SQL code that uses
lots temp tables and the like into a SQL Server 2005 only version,
possibly using CTE.

What I want to achieve is a menu like that on
http://www.cancerline.com/cancerlin...9801_6_3_3.aspx

Notice how you have the top level menu items, and then child nodes
expanded down to the low level page that link sends you to.

Table sql:

CREATE TABLE [dbo].& #91;NavigationNode](

[NodeId] [int] primary key nonclustered,
[Text] [nvarchar](150) COLLATE  SQL_Latin1_General_C
P1_CI_AS NULL,
& #91;AlternativeText]
 [nvarchar](250) COLLATE  SQL_Latin1_General_C
P1_CI_
AS
NULL,
[Level] [int] NULL,
[ParentNodeId] [int] constraint fk_parent_navnode
foreign key references [NavigationNode] ([NodeId])
)

Table data:

1	Home	NULL	1	NULL
 2	Solutions	NULL	1	1

3	Solutions child1	NULL	2	2
4	Solutions child2	NULL	2	2
5	Contact	NULL	1	1
6	solutions child1 child1	NULL	3	3
7	solutions child1 child2	NULL	3	3
8	solutions child1 child3	NULL	3	3
9	solutions child1 child4	NULL	3	3
10	contact child1	NULL	2	5

I have started to write some code with the common table expression
syntax:

declare @root int;
set @root = 2;

WITH Nav([NodeId],[ParentNodeId], [Text],  [Level]) AS
(
SELECT [NodeId], [ParentNodeId], [Text], [Level]
FROM [dbo].[NavigationNode]
WHERE [ParentNodeId] = @root

UNION ALL

SELECT n1.[NodeId], n1.[ParentNodeId], n1.[Text], n1.[Level]
FROM [dbo].[NavigationNode] n1
INNER JOIN Nav n2
ON n1.[ParentNodeId] = n2.[NodeId]

)
SELECT *
FROM Nav

Which returns:

3	2	Solutions child1	2
4	2	Solutions child2	2
6	3	solutions child1 child1	3
7	3	solutions child1 child2	3
8	3	solutions child1 child3	3
9	3	solutions child1 child4	3

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.

-Brian

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/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx