Thanks Daniel, will look into it.
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 Daniel Crowther Sent: Monday, 8 October 2007 10:32 AM To: [email protected] Subject: [cfaussie] Re: Having a bad day with this problem Andrew Are you using MSSQL? Here is a UDF that will return a list of the parentids, 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 @TreeidEND To use it just do something like SELECT * , dbo.AgentTree(Agentid) FROM agents Regards Daniel --~--~---------~--~----~------------~-------~--~----~ 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 -~----------~----~----~----~------~----~------~--~---
