Does that only allow a set level of "depth"? Ideally, it should allow nth level relationships, eh?
Matthew P. Smith Web Developer, Object Oriented Naval Education & Training Professional Development & Technology Center (NETPDTC) (850)452-1001 ext. 1245 [EMAIL PROTECTED] -----Original Message----- From: Margaret Fisk [mailto:[EMAIL PROTECTED]] Sent: Tuesday, July 16, 2002 11:03 AM To: CF-Talk Subject: RE: Recursive queries This is a very complex query that I used to use to generate a report from: ID Parent Name 1 0 Dad 2 1 Daughter 3 2 Grandson To this: Master_Parent Parent Topic count Dad Dad Daughter 1 Dad Daughter Grandson 1 Or something like that...its been a while. Is this what you're looking for? It requires a known number of topic relationships SELECT s4.sort_order,' '||substr(rtrim(s3.Name),1,length(s3.name)) Master_Parent, ' '||substr(rtrim(s2.Name),1,length(s2.name)) Parent, ' '||substr(rtrim(s.Name),1,length(s.name)) Topic, ' '||rtrim(c.URL) URL, ' '||sum(c.Count) CountOfURL FROM pbls_$partner s,pbl_count_$partner c,sortorder_$partner s2, pbls_$partner s3, sortorder_$partner s4 WHERE s.pbl_id=c.pbl_id and s2.pbl_id(+)=s.father_id and s2.master_father_id=s3.pbl_id(+) and s.pbl_id=s4.pbl_id and c.hit_date in (select hit_date from temp_pbl2_$partner) GROUP BY s.path, s2.name, s.father_id, s.name,c.URL, s3.name, s4.sort_order; -----Original Message----- From: Smith, Matthew P -CONT(DYN) [mailto:[EMAIL PROTECTED]] Sent: Tuesday, July 16, 2002 8:57 AM To: CF-Talk Subject: RE: Recursive queries I looped this: SELECT * WHERE PARENT_ID IN (#keyList#) appending the keys returned to a list, and kept going till recordcount = 0 That goes from parent to all children, grandchildcren, all the way down. Going up is the opposite. I can chunk you the code if you want. Matthew P. Smith Web Developer, Object Oriented Naval Education & Training Professional Development & Technology Center (NETPDTC) (850)452-1001 ext. 1245 [EMAIL PROTECTED] -----Original Message----- From: chris.alvarado [mailto:[EMAIL PROTECTED]] Sent: Tuesday, July 16, 2002 10: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
