Querying a recursive relationship

Transact-SQL
1

Hello,

I'm trying to construct a query to pull data from tables that have a recursive relationship. I've found some examples, but none that fit my use case. Here's a simplified version of what I'm trying to accomplish.

CREATE TABLE #folders (
[ID] [int] NOT NULL,
[FolderName] varchar NULL,
[ParentID] [int] NULL
);

CREATE TABLE #files (
[ID] [int] NOT NULL,
[FileName] varchar NULL,
[FolderID] [int] NULL
);

insert into #folders values
(1,'Root',0),(2,'Appl',1),(3,'Data',1),(4,'Simulator',2),(5,'V1',4),(6,'V2',4),(7,'V3',4),(8,'Backup',5),
(9,'Backup',6),(10,'Backup',7),(11,'Temp',8),(12,'Install',11),(13,'Lab',3),(14,'Plant',3),(15,'Device1',13),(16,'Device2',13),
(17,'Tags',16),(18,'Zone1',14),(19,'Zone2',14),(20,'Zone3',14),(21,'PSep',18),(22,'TSep',18),(23,'VRU',18),(24,'PSep',19),(25,'VRU',19),(26,'Tank',19);

insert into #files values
(1,'File1',6),(2,'File2',8),(3,'File3',8),(4,'File4',11),(5,'File5',15),
(6,'File6',15),(7,'File7',21),(8,'File8',22),(9,'File9',23),(10,'File10',24);

drop table #folders
drop table #files

The output should list each file preceded by it's full path:

\Root\Appl\Simulator\V2\File1
\Root\Appl\Simulator\V1\Backup\File2
\Root\Appl\Simulator\V1\Backup\File3
\Root\Appl\Simulator\V1\Backup\Temp\File4
\Root\Data\Lab\Device1\File5
\Root\Data\Lab\Device1\File6
\Root\Data\Plant\Zone1\PSep\File7
\Root\Data\Plant\Zone1\TSep\File8
\Root\Data\Plant\Zone1\VRU\File9
\Root\Data\Plant\Zone2\PSep\File10

Any help would be greatly appreciated!

Thanks..Doug

2 
;with myCTE AS
(SELECT id, cast(folderName as varchar(4000)) as folderName, ParentID, cast('\'+ folderName as varchar(4000)) as fullPath
 FROM #folders as f
 WHERE ParentID = 0
 
 UNION ALL 
 
 SELECT f.id, cast(f.folderName as varchar(4000)), f.ParentID, cast(fullPath + '\' + f.folderName  as varchar(4000))
 FROM myCte as C
    iNNER JOIN #folders as f
    ON C.id = f.ParentID
)

select c.fullPath + '\' + f.fileName as fullPath
from myCTE as c
   INNER JOIN #files as f
   ON c.id = f.folderid

output:

fullPath
\Root\Appl\Simulator\V2\File1
\Root\Appl\Simulator\V1\Backup\File2
\Root\Appl\Simulator\V1\Backup\File3
\Root\Appl\Simulator\V1\Backup\Temp\File4
\Root\Data\Lab\Device1\File5
\Root\Data\Lab\Device1\File6
\Root\Data\Plant\Zone1\PSep\File7
\Root\Data\Plant\Zone1\TSep\File8
\Root\Data\Plant\Zone1\VRU\File9
\Root\Data\Plant\Zone2\PSep\File10

dbfiddle

3

Dang, it's amazing how some of you guys make hard stuff look easy. Awesome work, thank you very much!

4

My pleasure!