Not sure if SQL Server has it, but Oracle has a START WITH CONNECT BY clause that I've used to display a menu. The table had a parentID and a childID, childern could be parents of other chlidren, for as far as you wanted to go. It looks like you'd have to go the recursive way (from the 2 minutes of googling I did). So if you don't want to go that route, Do what Maureen suggested, QoQs are nice.
On Sat, Mar 24, 2012 at 9:02 PM, Maureen <[email protected]> wrote: > > I would select the entire table in a query sorted by sort order, then > loop over that query and do a query of queries to get the children and > grandchildren for each record. > > On Sat, Mar 24, 2012 at 1:56 PM, Eric Bourland <[email protected]> wrote: >> >> ColdFusion 9 >> SQL Server 2005 >> >> Hi friends. Do you have time to consider a problem? I am trying to display a >> list of Parent and Child pages, with children arranged under their >> respective parents, thus: >> >> Parent >> Child >> Child >> Child >> Parent >> Child >> Child >> Child >> Grandchild >> Grandchild >> >> ...etc. There will be no more than three levels of pages: Parent, child, >> grandchild. >> >> If possible, I would rather not get into CTE recursion, And it would be very >> onerous and forbidding to change my existing data tables. Instead, I am >> wondering, is there simply a way to do this in ColdFusion, using a query and >> some JOINs, and the correct cfoutput? Here is what I have so far: >> >> <!--- get table columns for parent, child, grandchild records---> >> <cfquery name="getPages" datasource="#APPLICATION.dataSource#"> >> SELECT p.PageID >> ,p.PageTitle >> ,p.DateCreated >> ,p.DateModified >> ,p.SortOrder >> ,c.PageID >> ,c.PageTitle >> ,c.DateCreated >> ,c.DateModified >> ,c.SortOrder >> ,c2.PageID >> ,c2.PageTitle >> ,c2.DateCreated >> ,c2.DateModified >> ,c2.SortOrder >> ,p.parentID >> ,c.parentID >> ,c2.parentID >> >> FROM #REQUEST.contentTable# p >> left join #REQUEST.contentTable# c on c.parentID = p.pageID >> left join #REQUEST.contentTable# c2 on c2.parentID = c.PageID >> ORDER BY p.SortOrder, p.PageTitle, c.SortOrder, c.PageTitle, c2.SortOrder, >> c2.PageTitle >> </cfquery> >> >> >> And the output: >> <!--- display record information ---> >> <cfoutput query="getPages" group="PageTitle"> >> <tr> >> <td>#getPages.PageID#</td> >> <td>#getPages.PageTitle#</td> >> <td>#DateFormat(getPages.DateCreated, "mmmm d, yyyy")#</td> >> <td>#DateFormat(getPages.DateModified, "mmmm d, yyyy")#</td> >> <td>#getPages.SortOrder#</td> >> </tr> >> </cfoutput> >> >> But, that gets me just a list of the pages, sorted in no particular order: >> http://www.hardrockteam.org/managepages2.cfm >> >> Does anyone have any ideas to sort these pages as I noted above? >> >> Thank you for any advice. >> >> Eric >> >> >> >> > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:350524 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
