If you don't choose to add those fields(I recommend you follow S. Isaac's method which is actually something from a guy named joe celko for which you can search google for) then you can do a udf in cfMX which calls a query which calls itself for each child. Or in cf5, you have to write a custom tag that calls itself. Either way, this recursive stuff is fairly hard on the server whereas Joe Celkos method is very efficient if a bit more confusing. DRE
-----Original Message----- From: S. Isaac Dealey [mailto:[EMAIL PROTECTED]] Sent: Tuesday, July 16, 2002 10:47 AM To: CF-Talk Subject: Re: 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? I've done this on many occasions... My suggestion is a non-temp, that is permanent cross-reference table... much faster than anything else I've seen . It looks something like this: table 1 ID NAME PARENT_ID TREEDEPTH table 2 ID PARENT_ID If possible, create an insert/update trigger on table 1 which looks something like this: CREATE TRIGGER trTable1Inherits ON table1 FOR INSERT, UPDATE AS -- only perform the trigger action if the parent_id was inserted/updated IF UPDATE(PARENT_ID) BEGIN -- delete cross-reference info for inserted or updated records and any child records DELETE FROM table2 WHERE ID IN (SELECT ID FROM INSERTED) OR PARENT_ID IN (SELECT ID FROM INSERTED); -- for all inserted or updated records, insert themselves into the xref table INSERT INTO table2 ( ID, PARENT_ID ) SELECT ID, ID FROM table1 WHERE NOT EXISTS (SELECT ID FROM table2 WHERE ID = table1.ID AND PARENT_ID = table1.ID); -- check for parent records not reflected in the xref table WHILE EXISTS (SELECT parenttable.ID FROM table1 parenttable INNER JOIN table2 xref ON (parenttable.ID = xref.parent_id) INNER JOIN table1 childtable ON (childtable.ID = xref.id) WHERE NOT EXISTS (SELECT ID FROM table2 xref2 WHERE xref2.ID = childtable.id AND xref2.parent_ID = parenttable.ID) ) BEGIN -- insert the parent records found into the xref table INSERT INTO table2 (ID, PARENT_ID) SELECT childtable.id, parenttable.ID FROM table1 parenttable INNER JOIN table2 xref ON (parenttable.ID = xref.parent_id) INNER JOIN table1 childtable ON (childtable.ID = xref.id) WHERE NOT EXISTS (SELECT ID FROM table2 xref2 WHERE xref2.ID = childtable.id AND xref2.parent_ID = parenttable.ID) ) END -- update the tree depth for all modified child records UPDATE table1 SET TreeDepth = (SELECT COUNT(ID) FROM table2 WHERE ID = table1.ID) WHERE ID IN (SELECT ID FROM table2 WHERE PARENT_ID IN (SELECT ID FROM INSERTED)); END You have to be really careful with that while loop and you may want to add a timer to break out of it if it goes on too long just as a precaution against the possibility of an infinite loop if something goes wrong... Once you've got this in place and each record in table 1 has a cross-reference in table 2 for each ancestor, then you can draw the tree view query very simply with something like: SELECT ID, NAME, PARENT_ID, TREEDEPTH FROM table1 [ WHERE ID IN (SELECT ID FROM table2 WHERE parent_id = #topofbranch#) ] GROUP BY PARENT_ID, TREEDEPTH, ID, NAME ORDER BY Name The result of this query can then be fed to either a recursive custom tag or a recursive UDF along with a parent_id to display the tree. The optional where clause will limit the results of the query to a given branch... To get all of the ancestors for a given entry in order of decendance, you would use: SELECT * FROM table1 WHERE ID IN (SELECT PARENT_ID FROM table2 WHERE ID = #childrecord#) ORDER BY TreeDepth This turns out to be quite fast, often in spite of the trigger, since what you were doing any time the tree is viewed is now only being done when an item is added or moved to a new location within the tree, which generally speaking happens much less often. hth and I hope it's not too long or confusing, Isaac Dealey www.turnkey.to 954-776-0046 ______________________________________________________________________ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.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
