Getting root from Hierarchical record

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Getting root from Hierarchical record

Author

Topic

akashenk
Posting Yak Master

111 Posts

Posted - 2008-08-25 : 11:55:58

I have a table [Folders] which contains hierarchical records with fields

Folder_ID
Parent_ID

I have a resultset populated with various Folder_Ids and I am trying to come up with a function which returns either a table or comma-delimieted string of 'Root_Ids'. Where the root_Id is the root parent_id for the folder within it's hierarchy. So, assuming I have data that looks like:

Folder_ID, Parent_ID

"Folder1", NULL
"Folder2", "Folder1"
"Folder3", "Folder2"
"Folder4", NULL
"Folder5", "Folder4"

If I have the resultset "Folder3", "Folder5", I would like to create a function which takes this as a parameter and returns either the resultset "Folder1", "Folder4", or a delimited string, like "Folder1,Folder4". As an option, I am able to pass in a delimited string, like "Folder3,Folder5" and create a temporary table from this within the function.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-08-25 : 12:00:19

Write a recursive CTE. There are examples in Books Online.

E 12°55'05.25"
N 56°04'39.16"

akashenk
Posting Yak Master

111 Posts

Posted - 2008-08-25 : 12:12:34

quote:
Originally posted by Peso

Write a recursive CTE. There are examples in Books Online.

I have been able to write a recursive CTE (below) that obtains the entire hierarchy, based on given root folderIds, however, I have been unable to create one which works backwards through the hierarchy to get the root

CREATE FUNCTION [dbo].[f_getFolderTree]
(
@folderIds varchar(max)
)
RETURNS varchar(max)
AS
BEGIN
-- Declare the return variable here
DECLARE @Result varchar(max)
SET @Result=''

DECLARE @rootFolders TABLE (folderid nchar(15) NOT NULL PRIMARY KEY)

INSERT @rootFolders
SELECT str FROM f_IterCharListToTable(@folderIds, ',')

DECLARE @selectedFolders TABLE (folderid nchar(15) NOT NULL PRIMARY KEY)

BEGIN

WITH FolderTree (folderid)

AS
(
-- Anchor member definition
SELECT ff.folder_id
FROM dbo.[FileFolders] ff
WHERE ff.folder_id IN (SELECT folderid FROM @rootFolders)

UNION ALL

-- Recursive member definition
SELECT ff.folder_id
FROM dbo.[FileFolders] ff
INNER JOIN FolderTree ft ON (ff.parent_id = ft.folderid)
WHERE ff.isdeleted=0

)

-- Statement that executes the CTE
INSERT @selectedFolders

SELECT folderId
FROM FolderTree

SELECT @Result = COALESCE(@Result + ',' + folderid, folderid) FROM @selectedFolders

END

RETURN @Result

END

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2008-08-25 : 13:04:34

I didn't bother with the list parsing, but here is a recursive CTE to traverse up a hierarchy:

DECLARE @Folder TABLE (Folder_ID VARCHAR(15), Parent_ID VARCHAR(15))

DECLARE @StartNode VARCHAR(15)
--SET @StartNode = 'Folder5'
SET @StartNode = 'Folder3'

INSERT @Folder
SELECT 'Folder1', NULL
UNION ALL SELECT 'Folder2', 'Folder1'
UNION ALL SELECT 'Folder3', 'Folder2'
UNION ALL SELECT 'Folder4', NULL
UNION ALL SELECT 'Folder5', 'Folder4'
;

WITH FolderTree
AS
(
	SELECT Folder_ID, Parent_ID
	FROM @Folder
	WHERE Folder_ID = @StartNode

	UNION ALL

	SELECT F.Folder_ID, F.Parent_ID
	FROM @Folder AS F
	INNER JOIN FolderTree AS FT
	ON F.Folder_ID = FT.Parent_ID
)

SELECT *
FROM FolderTree
WHERE Parent_ID IS NULL

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2008-08-25 : 13:07:53

Opps, well the above example is a simpler one, but I cut-n-pasted the wrong version. Here is the one I ment to post that uses your "parsed" string table:

DECLARE @Folder TABLE (Folder_ID VARCHAR(15), Parent_ID VARCHAR(15))

INSERT @Folder
SELECT 'Folder1', NULL
UNION ALL SELECT 'Folder2', 'Folder1'
UNION ALL SELECT 'Folder3', 'Folder2'
UNION ALL SELECT 'Folder4', NULL
UNION ALL SELECT 'Folder5', 'Folder4'

DECLARE @Nodes TABLE (Folder_ID VARCHAR(15))

INSERT @Nodes
SELECT 'folder5'
UNION ALL SELECT 'folder3'
;

WITH FolderTree
AS
(
	SELECT F.Folder_ID, F.Parent_ID
	FROM @Folder AS F
	INNER JOIN @Nodes AS N
	ON F.Folder_ID = N.Folder_ID

	UNION ALL

	SELECT F.Folder_ID, F.Parent_ID
	FROM @Folder AS F
	INNER JOIN FolderTree AS FT
	ON F.Folder_ID = FT.Parent_ID
)

SELECT *
FROM FolderTree
WHERE Parent_ID IS NULL

akashenk
Posting Yak Master

111 Posts

Posted - 2008-08-25 : 20:58:22

Thanks for your help!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources