Thanks Brett.
Andrew Scott Senior Coldfusion Developer Aegeon Pty. Ltd. www.aegeon.com.au Phone: +613 8676 4223 Mobile: 0404 998 273 -----Original Message----- From: [email protected] [mailto:[EMAIL PROTECTED] On Behalf Of Brett Payne-Rhodes Sent: Monday, 8 October 2007 10:23 AM To: [email protected] Subject: [cfaussie] Re: Having a bad day with this problem This article on recursive queries is SQL Server 2005 might be useful: http://www.sqlservercentral.com/articles/Development/recursivequeriesinsqlse rver2005/1760/ And there is a preliminary article that introduces 'Common Table Expressions': http://www.sqlservercentral.com/articles/Development/commontableexpressionsi nsqlserver2005/1758/ You have to sign up to read the articles but I don't appear to be getting any spam to the address that I signed up with. I'd be interested to hear how you get on... Cheers, Brett B) Andrew Scott wrote: > All the parents. > > > > > > Andrew Scott > Senior Coldfusion Developer > Aegeon Pty. Ltd. > www.aegeon.com.au <http://www.aegeon.com.au> > Phone: +613 8676 4223 > Mobile: 0404 998 273 > > > > > > *From:* [email protected] [mailto:[EMAIL PROTECTED] *On > Behalf Of *Daniel Crowther > *Sent:* Monday, 8 October 2007 9:53 AM > *To:* [email protected] > *Subject:* [cfaussie] Re: Having a bad day with this problem > > > > Andrew > > > > And you have to get all the parents or just the parent and its parent. > > > > Regards > > Daniel > > > > *From:* [email protected] [mailto:[EMAIL PROTECTED] *On > Behalf Of *Andrew Scott > *Sent:* Monday, 8 October 2007 9:46 AM > *To:* [email protected] > *Subject:* [cfaussie] Re: Having a bad day with this problem > > > > Daniel thanks, but the number of children can be from 2-13+ deep. > > > > > > Andrew Scott > Senior Coldfusion Developer > Aegeon Pty. Ltd. > www.aegeon.com.au <http://www.aegeon.com.au> > Phone: +613 8676 4223 > Mobile: 0404 998 273 > > > > > > *From:* [email protected] [mailto:[EMAIL PROTECTED] *On > Behalf Of *Daniel Crowther > *Sent:* Saturday, 6 October 2007 10:33 AM > *To:* [email protected] > *Subject:* [cfaussie] Re: Having a bad day with this problem > > > > Andrew > > > > This following should work on MSSQL , if you know that there will only > ever be 3 levels in the tree. > > > > SELECT Child.Name, Parent.Name AS Parent, GrandParent.Name AS GrandParent, > > ISNULL(GrandParent.Name + ',', '') + ISNULL(Parent.Name > + ',', '') + ISNULL(Child.Name + ',', '') AS SortList > > FROM TableName Child LEFT JOIN > > TableName Parent ON Parent.Agentid = Child.Parentid LEFT > JOIN > > TableName GrandParent ON GrandParent.Agentid = > Parent.Parentid > > ORDER BY SortList > > > > You just need to add > > WHERE Child.Agentid = {the agent id you want} > > To select a single item. > > > > Regards > > Daniel Crowther > > > > *From:* [email protected] [mailto:[EMAIL PROTECTED] *On > Behalf Of *Andrew Scott > *Sent:* Friday, October 05, 2007 2:19 PM > *To:* [email protected] > *Subject:* [cfaussie] Re: Having a bad day with this problem > > > > Thanks Dale, > > > > Thats what I was thinking too. But I had it in my head it could be done. > > > > > > Andrew Scott > Senior Coldfusion Developer > Aegeon Pty. Ltd. > www.aegeon.com.au <http://www.aegeon.com.au> > Phone: +613 8676 4223 > Mobile: 0404 998 273 > > > > > > *From:* [email protected] [mailto:[EMAIL PROTECTED] *On > Behalf Of *Dale Fraser > *Sent:* Friday, 5 October 2007 1:02 PM > *To:* [email protected] > *Subject:* [cfaussie] Re: Having a bad day with this problem > > > > I dont think you can do this in one SQL statement. > > > > It could be done as a recursive function. > > > > If you are using CF8 grids, then you can use ajax to populate the > children, thus the query only returns the elements at the current depth. > > > > Regards > > Dale Fraser > > > > http://learncf.com > > > > *From:* [email protected] [mailto:[EMAIL PROTECTED] *On > Behalf Of *Andrew Scott > *Sent:* Friday, 5 October 2007 12:15 PM > *To:* [email protected] > *Subject:* [cfaussie] Having a bad day with this problem > > > > Being Friday I think I have to be having a brain drain. > > > > I have a table that has the following structure > > > > AgentId, Name, ParentId > > > > This is basically creating a tree that would have something like this > > > > Root > > -ParentName1 > > --ChildName_Parent1 > > ---ChildName1 > > ---ChildName1_1 > > -ParentName2 > > --ChildName_Parent2 > > ---Childname2 > > ---ChildName2_1 > > ---ChildName2_2 > > > > Now the problem I am having is that I am going to need to get at > ChildName2_2, get is parent and if the parent to that child has a parent. > > > > Can anyone see this in one SQL query? > > > > > > > > Andrew Scott > Senior Coldfusion Developer > Aegeon Pty. Ltd. > www.aegeon.com.au <http://www.aegeon.com.au> > Phone: +613 8676 4223 > Mobile: 0404 998 273 > > > > > > > > > > > > > > > > > > > style='font-size:12.0pt;font-family:"Times New Roman","serif"'> > > > > > > > > > > > > > --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "cfaussie" group. To post to this group, send email to [email protected] To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/cfaussie?hl=en -~----------~----~----~----~------~----~------~--~---
