CODEWITHSUNDEEP
sql-servert-sql
Jason Butera
Jason Butera

Reputation: 2450

SQL JOIN on list of IDs in a column

I've got a multi-tiered object in my database called MyFolder. MyFolder can be a child of another MyFolder at infinite levels. The table is defined as follows:

CREATE TABLE dbo.MyFolders
(
    MyFolderId INT IDENTITY(1,1) NOT NULL,
    ParentMyFolderId INT NULL,
    Name NVARCHAR(50) NOT NULL,
    Depth INT NOT NULL,
    Ancestry NVARCHAR(max) NOT NULL,
    CONSTRAINT PK_MyFolders PRIMARY KEY CLUSTERED (MyFolderId ASC),
    CONSTRAINT FK_MyFolders_MyFolders FOREIGN KEY(ParentMyFolderId) REFERENCES dbo.MyFolders (MyFolderId)
)

It has data like:

MyFolderId ParentMyFolderId Name           Depth Ancestry
1          NULL             Folder1        0     /
2          1                Folder1A       1     /1/
3          1                Folder1B       1     /1/
4          1                Folder1C       1     /1/
5          4                Folder1C1      2     /1/4/
6          4                Folder1C2      2     /1/4/
7          6                Folder1C2a     3     /1/4/6/
8          6                Folder1C2b     3     /1/4/6/

This works quite well for everything needed in my system. However, it gets tricky if I want to retrieve a query like the following:

MyFolderId Name
1          Folder1
2          Folder1/Folder1A
3          Folder1/Folder1B
4          Folder1/Folder1C
5          Folder1/Folder1C/Folder1C1
6          Folder1/Folder1C/Folder1C2
7          Folder1/Folder1C/Folder1C2/Folder1C2a
8          Folder1/Folder1C/Folder1C2/Folder1C2b

Is there a way to JOIN on the ancestry field in order to get the ancestor names? Or another way using the ParentMyFolderId column? I do have a table-valued split string function called SplitString(value, delimiter).

Upvotes: 1

Views: 358

Answers (2)

Cᴏʀʏ
Cᴏʀʏ

Reputation: 107566

Here's a recursive CTE as mentioned in the comments:

WITH TreeStructure(MyFolderId, Name) AS 
(
    SELECT MyFolderId, CONVERT(varchar(500), Name)
    FROM MyFolders WHERE ParentMyFolderId IS NULL
    UNION ALL
    SELECT sd.MyFolderId, CONVERT(varchar(500), t.Name + '/' + sd.Name)
    FROM MyFolders sd
    JOIN TreeStructure t ON sd.ParentMyFolderId = t.MyFolderId
    WHERE sd.ParentMyFolderId IS NOT NULL
)
SELECT * FROM TreeStructure

Results:

MyFolderId  Name
----------- ----------------------------------------
1           Folder1
2           Folder1/Folder1A
3           Folder1/Folder1B
4           Folder1/Folder1C
5           Folder1/Folder1C/Folder1C1
6           Folder1/Folder1C/Folder1C2
7           Folder1/Folder1C/Folder1C2/Folder1C2a
8           Folder1/Folder1C/Folder1C2/Folder1C2b

Upvotes: 0

Evaldas Buinauskas
Evaldas Buinauskas

Reputation: 14097

This can be done using recursive queries, just append your current folder name to what you had previously.

Query:

;WITH Source (MyFolderId, ParentMyFolderId, Name, Depth, Ancestry)
AS (
SELECT 1, NULL, 'Folder1', 0, '/'
UNION ALL
SELECT 2, 1, 'Folder1A', 1, '/1/'
UNION ALL
SELECT 3, 1, 'Folder1B', 1, '/1/'
UNION ALL
SELECT 4, 1, 'Folder1C', 1, '/1/'
UNION ALL
SELECT 5, 4, 'Folder1C1', 2, '/1/4/'
UNION ALL
SELECT 6, 4, 'Folder1C2', 2, '/1/4/'
UNION ALL
SELECT 7, 6, 'Folder1C2a', 3, '/1/4/6/'
UNION ALL
SELECT 8, 6, 'Folder1C2b', 3, '/1/4/6/'
),
cte AS
(
SELECT S.MyFolderID, S.ParentMyFolderId, CAST(S.Name AS VARCHAR(MAX)) AS Name
FROM Source AS S
WHERE ParentMyFolderId IS NULL
UNION ALL
SELECT S.MyFolderID, S.ParentMyFolderId, C.Name + '/' + S.Name
FROM Source AS S
INNER JOIN cte AS C
    ON C.MyFolderId = S.ParentMyFolderId
)
SELECT *
FROM cte

Upvotes: 1

Related Questions