[sqlite] With recursive question

[Igor Tandetnik]

On 2/28/2015 7:22 PM, Jean-Christophe Deschamps wrote:
> Once again thank you very much Igor. I was making my life miserable
> trying to scan the "tree" from the other end.

That, too, could be arranged. Something along these lines:

with recursive FileDirs as (
   select FileId, FileDirID ancestor, '' as path from Files
union all
   select FileId, ParentID, '/' || DirName || path
   from FileDirs join Dirs on (ancestor = DirId)
   where DirID != 0
)
select FileId, FileName, path
from FileDirs join Files using (FileId)
where ancestor = 0;

-- 
Igor Tandetnik