I may be missing something, but with recursive CTE's, why do you need the second table? I'm assuming it's just to speed things up once the counts get large?
sqlite> create table folders (id integer primary key, parent_id int references folders, name text not null collate nocase, check (not (parent_id is null and id != 1))); QUERY PLAN `--SEARCH TABLE sqlite_master USING INTEGER PRIMARY KEY (rowid=?) sqlite> insert into folders values (1, null, 'Folder1'), (2, 1, 'Folder2'), (3, 1, 'Folder3'), (4, 1, 'Folder4'), (5, 2, 'Folder5'), (6, 2, 'Folder6'); QUERY PLAN |--SCAN 6 CONSTANT ROWS `--SCAN TABLE folders Gonna use backslashes here rather than spaces. So admitidly not exactly what you were looking for. But some playing around with string functions should be able to get there. sqlite> with recursive foo (id, parent_id, name) as (select * from folders where id = 1 union all select folders.id, folders.parent_id, foo.name || '\' || folders.name from folders inner join foo on folders.parent_id = foo.id) select * from foo order by name; QUERY PLAN |--CO-ROUTINE 2 | |--SETUP | | `--SEARCH TABLE folders USING INTEGER PRIMARY KEY (rowid=?) | `--RECURSIVE STEP | |--SCAN TABLE foo | `--SEARCH TABLE folders USING AUTOMATIC COVERING INDEX (parent_id=?) |--SCAN SUBQUERY 2 `--USE TEMP B-TREE FOR ORDER BY id|parent_id|name 1||Folder1 2|1|Folder1\Folder2 5|2|Folder1\Folder2\Folder5 6|2|Folder1\Folder2\Folder6 3|1|Folder1\Folder3 4|1|Folder1\Folder4 -----Original Message----- From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Bart Smissaert Sent: Tuesday, January 29, 2019 10:52 AM To: General Discussion of SQLite Database Subject: [sqlite] Displaying hierarchical structure Working on an Android app and part of that is storing SQL in a virtual folder system in SQLite. For this I want to use a so-called closure table as explained nicely here: http://technobytz.com/closure_table_store_hierarchical_data.html I have a table holder the folder details: ID PARENT_ID Folder ------------------------------------- 1 0 Folder1 2 1 Folder2 3 1 Folder3 4 1 Folder4 5 2 Folder5 6 2 Folder6 And then the closure table: PARENT_ID CHILD_ID DEPTH ----------------------------------------------- 1 1 0 2 2 0 3 3 0 4 4 0 5 5 0 6 6 0 1 2 1 1 3 1 1 4 1 2 5 1 1 5 2 2 6 1 1 6 2 What should the SQL be to display the folders like this: Folder PARENT_ID Folder1 0 Folder2 1 Folder5 2 Folder6 2 Folder3 1 Folder4 1 RBS _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
