clear markdown compare help donate or buy diddy dollings comments/suggestions/bugs a leap of faith? bible365
clear markdown donate comments/suggestions/bugs a leap of faith? bible365 diddy dollings
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0. 818513 fiddles created (9184 in the last week).

CREATE TABLE tree (id INT, parent INT, name VARCHAR);
 hidden batch(es)


INSERT INTO tree VALUES (1,0,'root'), (2,1,'branch 1'), (3,1,'branch 2'), (4,2,'branch 3'), (5,2,'branch 4'), (6,4,'branch 5'), (7,6,'branch 6'), (8,3,'leaf 1'), (9,5,'leaf 2'), (10,7,'leaf 3');
10 rows affected
 hidden batch(es)


WITH RECURSIVE cte AS ( SELECT t1.id, t1.parent, t1.name FROM tree t1 LEFT JOIN tree t2 ON t1.id = t2.parent WHERE t2.parent is null UNION ALL SELECT t1.id, t1.parent, CONCAT(t1.name, ' > ', t2.name) FROM tree t1 JOIN cte t2 ON t1.id = t2.parent ) SELECT name FROM cte WHERE parent = 0;
name
root > branch 2 > leaf 1
root > branch 1 > branch 4 > leaf 2
root > branch 1 > branch 3 > branch 5 > branch 6 > leaf 3
 hidden batch(es)