I had to write a stored proc to recursively scan a tree structure. The only way I could find to do this was through the use of a temp table, and a second query.
Basically - Query 1(which is the entry point into the routine) would create the temp table, then call the recursive stored proc (which would populate the temp table). When the recursion was done, a Query 1 would return all records in the temp table. At the time, I couldn't find any other way to do this effectively (unless I wanted to do iterative loops, which was MUCH slower). I've yet to see any reference that says there's a better way. My two cents. Shawn Grover -----Original Message----- From: chris.alvarado [mailto:[EMAIL PROTECTED]] Sent: Tuesday, July 16, 2002 9:37 AM To: CF-Talk Subject: Recursive queries Still trying to decide how I like to perform some type of recursive query wth the following Table structure; ID NAME PARENT_ID If the current record IS the parent then the Parent ID is 0 Otherwise the child has the ID of the parent as it's Parent ID. Anyone have any good references on recursive queries to handle this? A way to spider down through the parents and children basically. Ive done this before using Stored Procs and temp tables, but there has to be an easier way. Any suggestions? -chris.alvarado [ application developer ] 4 Guys Interactive, Inc. http://www.4guys.com -- I can picture in my mind a world without war, a world without hate. And I can picture us attacking that world, because they'd never expect it. -- Jack Handy ______________________________________________________________________ Your ad could be here. Monies from ads go to support these lists and provide more resources for the community. http://www.fusionauthority.com/ads.cfm FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/[email protected]/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
