Andrew
Are you using MSSQL?
Here is a UDF that will return a list of the parentid's, if that helps.
CREATE FUNCTION dbo.AgentTree (
@Agentid int
)
RETURNS varchar(255)
AS
BEGIN
Declare @Treeid varchar(1000),
@CurrentAgentid int,
@Parentid int,
@Counter int
SET @Treeid = @Agentid
SET @CurrentAgentid = @Agentid
SET @Counter = 1
WHILE (LEN(@CurrentAgentid) > 0 AND @Counter < 50)
BEGIN
SELECT @Parentid = Parentid
FROM Agents
WHERE Agentid = @CurrentAgentid
IF LEN(@Parentid) > 0
SET @Treeid = cast(@Parentid
as varchar) + ',' + @Treeid
SET @CurrentAgentid = @Parentid
SET @Counter = @Counter + 1
END
RETURN @Treeid
END
To use it just do something like
SELECT *, dbo.AgentTree(Agentid) AS Expr1
FROM agents
Regards
Daniel
From: [email protected] [mailto:[EMAIL PROTECTED] On Behalf
Of Andrew Scott
Sent: Monday, 8 October 2007 10:03 AM
To: [email protected]
Subject: [cfaussie] Re: Having a bad day with this problem
All the parents.
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: 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
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
-~----------~----~----~----~------~----~------~--~---
