I have a SQL Server database with a self referencing table. The depth of the structure can be between 1 and 20

• ID - primary key
• Name - nvarchar(256)
• ParentID - int

Typical data:

I want to provide an Id and list all "parent" folders

Id=3 : output: "Folder1\Folder2\Folder3"

Id=4 : output: "Folder1\Folder2\Folder3\Folder4"

I looked at Writing a recursive SQL query on a self-referencing table and wrote the following:

declare @id int
set @id = 4
;with ItemPath as 
    (
        select a.Id, cast(a.Name as nvarchar(max)) as Name, a.ParentId
        from ParentChild a
        where a.Id = @id --Starting point

        union all

        select a.Id, a.Name    '/'   parent.Name, a.ParentId
        from ParentChild as a
        inner join ItemPath as parent on parent.ParentId = a.Id 
    )
select * 
from ItemPath

but this gives me several rows:

I just want the last result

CodePudding user response：

First, you are trying to filter values ​​in the wrong place. This should be done not in the CTE, but in the main query, since the CTE is used to build the tree and generate the Name value.

Second, you have an error in the CTE in the recursive part when joining the CTE. You have on parent.ParentId = a.Id but you need the opposite on a.ParentId = parent.Id.

So you query would be like this

declare @id int;
set @id = 4;

WITH folder_tree AS (
    SELECT 
        id, 
        CAST(name AS nvarchar(max)) AS name, 
        parentid 
    FROM folders 
    WHERE parentid IS NULL
    UNION ALL
    SELECT 
        f.id,
        CAST(ft.name   '/'   f.name AS nvarchar(max)),
        f.parentid 
    FROM folders f
    JOIN folder_tree ft ON f.parentid = ft.id
)
SELECT * FROM folder_tree
WHERE id = @id

Query output

You can check a working demo here