SQL Tree Filestructure - get filepath

Question

I have readonly access to a MSSQLServer database referencing files on the server.

Two tables are used to represents the file structure:

folderInfo
-------------------------------------------------
|  id  |  parentId  |  Sequence |  folderName   | 
|------------------------------------------------
|  1   |      0     |     1     |     folder1   |
|  2   |      0     |     2     |     folder2   |
|  3   |      1     |     1     |   subfolder1  |
-------------------------------------------------

fileInfo
-----------------------------------------------
|  id  |  folderId    |  Sequence | fileName  | 
|----------------------------------------------
|  1   |      3       |      1    |  e.xml    |
|  2   |      2       |      1    |  a.xml    |
|  3   |      2       |      2    |  f.xml    |
-----------------------------------------------

"parentId=0" means that the folder is in the root of the file structure.

I have no clue how to make a query for all files, with a union capable of giving me the filepath (relative to the root).

Given that the order of magnitude of the number of files is of 10000, I don't want to have to query again for each file (or am I wrong to think this is inefficient?).

I have found a beginning of an answer in that post: SQL Tree Structure

with recursive full_tree as (
  select id, name, parent, 1 as level
  from departments
  where parent is null
  union all 
  select c.id, c.name, c.parent, p.level + 1
  from departments c
    join full_tree p on c.parent = p.id
)
select *
from full_tree;

But I do not know how I can turn this into what I need, which would be something like:

fileInfo
----------------------------------------------------
|  id  |  Sequence | filepath                      | 
|---------------------------------------------------
|  1   |      1    |  /folder1/subfolder1/e.xml    |
|  2   |      1    |  /folder2/a.xml               |
|  3   |      2    |  /folder2/f.xml               |
----------------------------------------------------

John Cappelletti · Accepted Answer · 2016-08-18T19:05:53.433

Something like this

Declare @FolderInfo table (id int,parentId int,Sequence int, folderName varchar(100))
Insert into @FolderInfo values (1,0,1,'folder1'),(2,0,2,'folder2'),(3,1,1,'subfolder1')

Declare @FileInfo table (id int,folderId int,Sequence int, fileName varchar(100))
Insert into @FileInfo values (1 ,3 ,1,'e.xml'),(2 ,2 ,1,'a.xml'),(3 ,2 ,2,'f.xml')

;with cteHB (Seq,ID,parentId,Lvl,folderPath) as (
    Select  Seq  = cast(1000+Row_Number() over (Order by folderName) as varchar(500))
           ,ID
           ,parentId
           ,Lvl=1
           ,folderPath = cast('/'+folderName as varchar(500))
     From   @FolderInfo 
     Where  parentId = 0
     Union  All
     Select Seq  = cast(concat(cteHB.Seq,'.',1000+Row_Number() over (Order by cteCD.Sequence)) as varchar(500))
           ,cteCD.ID
           ,cteCD.parentId,cteHB.Lvl+1
           ,folderPath = cast(concat(cteHB.folderPath,'/',cteCD.folderName ) as varchar(500))
     From   @FolderInfo cteCD 
     Join   cteHB on cteCD.parentId = cteHB.ID)
Select B.ID
      ,B.Sequence
      ,folderPath=A.folderPath+'/'+B.fileName
 From cteHB A
 Join @FileInfo B on (B.folderId =A.ID)
 Order By A.Seq

Returns

ID  Sequence    folderPath
1   1           /folder1/subfolder1/e.xml
2   1           /folder2/a.xml
3   2           /folder2/f.xml

Thank you, SQL wizard ! Considering the database is a _SQLServer2008R2_, concat is not supported, so I used '+' instead. I ran into concatenation error (type mismatch stuff), so I used `cast(... as varchar(500))` to transform all `int` that were causing turmoil. — Nebular Noise, Aug 19 '16 at 09:09
@T4GG Sorry for the late response. cteHB (in my little head) is cte Hierarchy Build, and CD is Core Data. Just names that make sense to me — John Cappelletti, Aug 19 '16 at 13:32

SQL Tree Filestructure - get filepath

1 Answers1