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 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, That's 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 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 don't 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 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 -~----------~----~----~----~------~----~------~--~---
