WITH RECURSIVE subfolders AS (
SELECT id, name, NULL AS parent FROM folders WHERE parent_id IS NULL
UNION ALL
SELECT f.id, f.name, s.name FROM folders f
INNER JOIN subfolders s ON f.parent_id = s.id
) SELECT * FROM subfolders;
id
name
parent
1
home
2
root
3
user
home
4
documents
user
5
pictures
user
6
downloads
user
7
vacations
pictures
8
baltic sea
vacations
9
tatra mountains
vacations
…
hidden batch(es)
WITH RECURSIVE childs AS (
SELECT id, name, parent_id FROM folders WHERE name = 'baltic sea'
UNION ALL
SELECT f.id, f.name, f.parent_id FROM folders f
INNER JOIN childs c ON f.id = c.parent_id
) SELECT id, name FROM childs;