December 10, 2008 at 6:52 pm
I have a table I'm using for site navigation. I can query the top level pages by checking to see if the navigation record doesn't have a ParentNavigationID. What I want to accomplish is getting the number of child pages each top level navigation has in the same query.
Can someone provide a sample of how I would return:
NavigationID, NavigationText, ChildPages
Here's what I have so far:
SELECT NavigationID, NavigationText
FROM Navigation
WHERE ParentNavigationID IS NULL
ORDER BY Sort
Thanks!
December 10, 2008 at 7:08 pm
In which table do you store connections between each top level navigation and its child pages?
_____________
Code for TallyGenerator
December 10, 2008 at 7:29 pm
I think that this will do it.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
December 10, 2008 at 7:30 pm
Heh. Oops.
With cteNav as (
Select NavigationID, NavigationText, NavigationID as Root
From Navigation
Where ParentNavigationID IS NULL
UNION
Select NavigationID, NavigationText, P.Root as Root
From Navigation N
Join cteNav P ON N.ParentNavigationID = P.NavigationID
)
Select Root as NavigationID
, MAX(Case When Root = NavigationID Then NavigationText Else Null End) as NavigationText
, Count(*) as Children
From cteNav
Group By Root
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
December 10, 2008 at 7:47 pm
rbarryyoung (12/10/2008)
I think that this will do it.
I think not in SQL2000.
_____________
Code for TallyGenerator
December 10, 2008 at 7:49 pm
Right. Never mind... :blush:
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply