Thanks Justin, I'll have a proper checkout of that in a bit, looks quite involved- "The Zen of Recursion" lol
-----Original Message----- From: Justin [mailto:[EMAIL PROTECTED] Sent: 23 January 2004 16:08 To: [EMAIL PROTECTED] Subject: RE: [ cf-dev ] interesting SQL loop Look here http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsqlma g01/ html/recursion.asp you also can speed up these types of querys using the Nested Table Model, the NTM, ala Joe Celko http://www.intelligententerprise.com/001020/celko.shtml so this sql will give you a list of all the nodes up to the root SELECT P2.* FROM Personnel AS P1, Personnel AS P2 WHERE P1.lft BETWEEN P2.lft AND P2.rgt AND P1.emp = :myemployee; and I have a sp / script that transform your parent - child model to the NTM model is you want Justin -----Original Message----- From: Damian Watson [mailto:[EMAIL PROTECTED] Sent: 23 January 2004 15:40 To: [EMAIL PROTECTED] Subject: [ cf-dev ] interesting SQL loop Hi, Any ideas would be appreciated: I've got db content with an ID and a parentID. parentID can be null denoting a top of the tree piece of content. So I could have a content tree like: ID: 1 ParentID: NULL >ID: 2 ParentID: 1 >ID: 3 ParentID: 2 Potentially ad infinitum. The question is how would I create a query that could select the IDs for this lineage if I am starting with the lowest rung ID of 3. In short I'm trying to create a crumbtrail dynamically and I don't want to limit it to three rungs... Cheers d -- ** Archive: http://www.mail-archive.com/dev%40lists.cfdeveloper.co.uk/ To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] For human help, e-mail: [EMAIL PROTECTED] -- ** Archive: http://www.mail-archive.com/dev%40lists.cfdeveloper.co.uk/ To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] For human help, e-mail: [EMAIL PROTECTED] -- ** Archive: http://www.mail-archive.com/dev%40lists.cfdeveloper.co.uk/ To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] For human help, e-mail: [EMAIL PROTECTED]
