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
--~--~---------~--~----~------------~-------~--~----~
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
-~----------~----~----~----~------~----~------~--~---
