By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE `nstree` (
`sid` int primary key,
`left` int,
`right` int,
`level` int,
`name` varchar(255)
);
INSERT INTO `nstree` (`sid`, `left`, `right`, `level`, `name`) VALUES
(1, 1, 24, 0, 'Root'),
(2, 2, 9, 1, 'A'),
(3, 3, 4, 2, 'B'),
(4, 5, 8, 2, 'C'),
(5, 6, 7, 3, 'D'),
(6, 10, 19, 1, 'E'),
(7, 11, 18, 2, 'F'),
(8, 12, 13, 3, 'G'),
(9, 14, 17, 3, 'H'),
(10, 15, 16, 4, 'I'),
(11, 20, 23, 1, 'J'),
(12, 21, 22, 2, 'K');
Records: 12 Duplicates: 0 Warnings: 0
SELECT n.sid, n.name, GROUP_CONCAT(p.sid ORDER BY p.left) ancestors,
CASE WHEN n.level = 1 THEN n.sid ELSE MAX(CASE WHEN p.level = 1 THEN p.sid END) END l1_ancestor
FROM nstree n
LEFT JOIN nstree p ON p.left < n.left AND p.right > n.right
GROUP BY n.sid;
| sid | name | ancestors | l1_ancestor |
|---|---|---|---|
| 1 | Root | null | null |
| 2 | A | 1 | 2 |
| 3 | B | 1,2 | 2 |
| 4 | C | 1,2 | 2 |
| 5 | D | 1,2,4 | 2 |
| 6 | E | 1 | 6 |
| 7 | F | 1,6 | 6 |
| 8 | G | 1,6,7 | 6 |
| 9 | H | 1,6,7 | 6 |
| 10 | I | 1,6,7,9 | 6 |
| 11 | J | 1 | 11 |
| 12 | K | 1,11 | 11 |