1 recursive field in a Query

[Simon_Whale]

you need to expand your question with code sample and data sample. as my first questions would be is the data heirachical? or is it based on columns through out the row?

As barmey as a sack of badgers Dude, if I knew what I was doing in life, I'd be rich, retired, dating a supermodel and laughing at the rest of you from the sidelines.

[Alegria_Lee]

Do you mean ,with a given root node to get a whole tree ?

[Herman T Instance]

To give more details. 1 table called documents holds documentnames and have a link to 1 table called documentfolders and this table has a link to documentid and folderid 1 table called folders and holders foldernames, folderid and parentfolderid. structure to retrieve is folder1/folder2/folder3/foldern for the complete path in which the document can be found. If I only ask documentname and foldername it would be simple. but once I have to foldername I should retrieve the previous level of foldername recusrively until no foldername is found. Basically it knows 'foldern' but should find the way back to root ('folder1' in example) So my query should do something like SELECT DocumentName, GetVirtualPath(documentID, FOLDERID) AS virtualPath FROM Documents as d Join Documentfolders as df on df.documentID = d.documentID Join Folders as f on f.folderID = df.folderID GetVirtualPath(documentID, FOLDERID) should be the recursive call.

In Word you can only store 2 bytes. That is why I use Writer.

modified on Thursday, October 14, 2010 8:49 AM

[Herman T Instance]

No its an endnode that should retrieve the way back to rootnode

In Word you can only store 2 bytes. That is why I use Writer.

[Chris Meech]

You could try writing a function that calls itself and returns the parent folder name for a given parent folder id. Eventually you should end up at root, where the parent folder id should be null and that would end the recursive call. :)

Chris Meech I am Canadian. [heard in a local bar] In theory there is no difference between theory and practice. In practice there is. [Yogi Berra]

[Herman T Instance]

Since this system is build and delivered by an external party I am not allowed to build a function in this database (warranty story....)

In Word you can only store 2 bytes. That is why I use Writer.

[Simon_Whale]

if your using SQL 2005 or later. Have a look into recursive CTE (common table expressions)

As barmey as a sack of badgers Dude, if I knew what I was doing in life, I'd be rich, retired, dating a supermodel and laughing at the rest of you from the sidelines.

[Herman T Instance]

It is an Interbase database but hat can do CTE as well. But the select results is multiple rows and not 1 varchar with the result like desired.

In Word you can only store 2 bytes. That is why I use Writer.

[Mycroft Holmes]

Since you cannot make changes to the database and you don't want the information in it's natural state, rows from the CTE then you need to do the manipulation in your client app. Take the results of the CTE and massage it to meet your needs.

Never underestimate the power of human stupidity RAH

[Herman T Instance]

It was a bit of a headache but my colleague came up with materialized path design pattern:

WITH 
	ParentChildRels
	(ParentID, ChildID, KeyField, [Path], HierarchyLevel, Doc) 
AS 
(
	SELECT     
		ParentID, ChildID, KeyField, CAST('root/' + Folder AS nvarchar(255)) AS Path, 1 AS HierarchyLevel, Doc 
	FROM         
	(
		SELECT     
			dbo.Folder.ParentID, dbo.Folder.ID AS ChildID, dbo.Folder.Folder, dbo.[Document].DocumentID AS KeyField, dbo.[Document].[Document] AS Doc
		FROM          
			dbo.[Document] 
		RIGHT OUTER JOIN
			dbo.Folder 
		ON 
			dbo.[Document].FolderID = dbo.Folder.ID
	) AS x
	WHERE     
		(ParentID = 0)
	
	UNION ALL


	SELECT     
		r.ParentID, r.ChildID, r.KeyField, CAST(RTRIM(pr.Path) + '/' + r.Folder AS nvarchar(255)) AS Path, pr.HierarchyLevel + 1 AS HierarchyLevel, r.Doc
	FROM         
	(
		SELECT     
			Folder_1.ParentID, Folder_1.ID AS ChildID, Folder_1.Folder, Document_1.DocumentID AS KeyField, Document_1.[Document] AS Doc
		FROM          
			dbo.[Document] AS Document_1 
		INNER JOIN
			dbo.[Folder] AS Folder_1 
		ON
			Document_1.FolderID = Folder_1.ID
	) AS r 
	INNER JOIN
		ParentChildRels AS pr 
	ON r.ParentID = pr.ChildID
)
SELECT DISTINCT TOP (100) PERCENT 
	ParentID, ChildID, KeyField, RTRIM(RTRIM([Path]) + '/' + Doc)
FROM         
	ParentChildRels AS ParentChildRels_1
WHERE Keyfield is not null
ORDER BY 
	RTRIM(RTRIM([Path]) + '/' + Doc), ParentID, ChildID, KeyField

I thought to share it with you all if you ever come into the same situation

In Word you can only store 2 bytes. That is why I use Writer.

modified on Monday, October 18, 2010 4:59 AM