I had to face this same problem when I decided to build my own Content Mangement System. I wrestled with the whole menu/child/parent thing for ages till someone used the term 'nested sets' which I hadn't heard before. I did some research and found it to be the perfect answer for me.
You can do whole trees in one query. You can get parts of the tree with all children (or conversely with all parents for the breadcrumbs) in a single query. A neat thing about nested sets is it's really simple (and fast!) to cut off a branch of the tree with everything still attached and graft it on anywhere else you like. It takes a little longer to insert a page, but then you do that once a day once a week, while you are calling up the menu or sitemap or breadcrumb several times a second. It's far more important to get performance improvements on the display site of the site than the CMS side. So a sacrifice of a little performance in the CMS is worth it to gain simplicity and performance on the public side. There are even some ready-made ColdFusion applications for nested sets too. If you want you can just take theirs and get to work. Barney Boisvert wrote his treemanger.cfc and it works a treat. He has his blog about it and the download for it at http://barneyb.com/barneyblog/index.php?s=treemanager&submit=Search and you can read a review of how to use it http://www.remotesynthesis.com/blog/index.cfm/2005/11/4/Managing-Tree-Using- TreeManagercfc-and-Flash-Forms There's an article about various methods of handling trees that covers nested sets in some detail at http://searchoracle.techtarget.com/tip/1,289483,sid13_gci537290,00.html Oh and did I say I think nested sets is the way to go for managing menu trees and site structure? Cheers Mike Kear Windsor, NSW, Australia Adobe Certified Advanced ColdFusion Developer AFP Webworks Pty Ltd http://afpwebworks.com Full Scale ColdFusion hosting from A$15/month -----Original Message----- From: Duane [mailto:[EMAIL PROTECTED] Sent: Wednesday, 15 November 2006 3:35 AM To: SQL Subject: Recursion in SQL ??? Is it possible to create a recursive query in SQLServer? I have a table that contains a parent/child record relationship and I want to build a tree without client side processing. ID Parent 1 Null 2 1 3 Null 4 2 5 1 6 Null 7 2 8 6 Needs to be sorted as -1 - 2 - 4 - 7 - 5 -3 -6 - 8 TIA, Duane ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/groups/SQL/message.cfm/messageid:2626 Subscription: http://www.houseoffusion.com/groups/SQL/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.6
