All, Thank you again for all of your suggestions. As time allows, I will probably play with all of them just to learn how.
It is wonderful to have such support. Take care, Robert ---------- Original Message ---------------------------------- From: S. Isaac Dealey <[EMAIL PROTECTED]> Reply-To: [EMAIL PROTECTED] Date: Fri, 13 Sep 2002 08:56:59 -0400 >> S. Isaac Dealey wrote: >>> Before you give up... There's a couple of things you can >>> do for the >>> recursion in the db... One is to create a cross-reference >>> table which shows >>> the parent-child relationship between all records in the >>> table... >>> Alternatively, you could try this: >>> >>> <cfset rs = QueryNew("ID")> >>> <cfset temp = QueryAddRow(rs)> >>> <cfset rs.id[1] = "0"> >>> <cfset rs2 = QueryNew("ID")> >>> >>> <cfloop condition="rs.recordcount"> >>> <cfquery name="rs" datasource="..."> >>> SELECT * FROM mytable >>> WHERE parentid IN (#valuelist(rs.id)#) >>> </cfquery><cfquery name="rs3" dbtype="query"> >>> SELECT * FROM rs UNION SELECT * FROM rs2 >>> </cfquery><cfset rs2 = rs3> >>> </cfloop> >>> >>> This or something like it ought to give you a single >>> query containing all of >>> the children of the parent... It will be much slower than >>> a cross-reference >>> table in the db, however, it should be similar in >>> performance to the current >>> solution... Once you have this query, however, you can >>> then use the >>> recursive UDF in CF 5 and either pass the query to the >>> UDF along with the >>> current parent id for the tree, or you can place the >>> query in the request >>> scope and reference it from there... >> >> never do to much work in cf when your database can do it >> faster > >Well, that's why I recommended that a cross-reference table would be >faster... :) All my recursion is done using the NTM model described by that >guy who wrote the book "SQL for Smarties" ... don't remember his name -- >never actually read the book -- discovered the method was documented after I >had figured out how to do it on my own... But if the page in question >doesn't see huge amounts of traffic, or if the tree structure isn't >tremendously deep ( width isn't much of a problem ) then it's not wholly >unreasonable in terms of performance to do it in cf as above... > > >Isaac >Certified Advanced ColdFusion 5 Developer > >www.turnkey.to >954-776-0046 > > ______________________________________________________________________ Signup for the Fusion Authority news alert and keep up with the latest news in ColdFusion and related topics. http://www.fusionauthority.com/signup.cfm FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists