[sqlite] With recursive question

[Igor Tandetnik]

On 2/28/2015 6:02 PM, Jean-Christophe Deschamps wrote:
> What I want to obtain is the list of all files (in random order but
> that's not the point) containing:
> FileID
> FileName
> Directory path from root using some kind of group_concat(dir, '/')

with recursive DirTree as (
   select DirID, '' as path from Dirs where DirName='root'

   union all

   select d.DirID, t.path || '/' || d.DirName
   from Dirs d join DirTree t on (d.ParentID=t.DirID)
   where d.DirName != 'root'
)
select FileId, FileName, path
from Files join DirTree on (FileDirID=DirID);

It's a bit unfortunate that you made the root a parent of itself. Forces 
the query to make an extra check to avoid infinite recursion.
-- 
Igor Tandetnik