By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE folders (
id int(10) unsigned NOT NULL AUTO_INCREMENT,
title nvarchar(255) NOT NULL,
parent_id int(10) unsigned DEFAULT NULL,
PRIMARY KEY (id),
INDEX (parent_id)
);
INSERT INTO folders(id, title, parent_id) VALUES
(1, 'root', null),
(2, 'one', 1),
(3, 'target', 2),
(4, 'child one', 3),
(5, 'child two', 3),
(6, 'root 2', null),
(7, 'other child one', 6),
(8, 'other child two', 6);
Records: 8 Duplicates: 0 Warnings: 0
create procedure get_related_nodes(in in_id int)
begin
set @list = in_id;
set @parents = @list;
repeat
set @sql = '
select group_concat(id) into @children
from folders
where parent_id in ({parents})
';
set @sql = replace(@sql, '{parents}', @parents);
prepare stmt from @sql;
execute stmt;
set @list = concat_ws(',', @list, @children);
set @parents = @children;
until (@children is null) end repeat;
set @child = in_id;
repeat
set @sql = '
select parent_id into @parent
from folders
where id = ({child})
';
set @sql = replace(@sql, '{child}', @child);
prepare stmt from @sql;
execute stmt;
set @list = concat_ws(',', @parent, @list);
set @child = @parent;
until (@parent is null) end repeat;
set @sql = '
select *
from folders
where id in ({list})
call get_related_nodes(3)
| id | title | parent_id |
|---|---|---|
| 1 | root | null |
| 2 | one | 1 |
| 3 | target | 2 |
| 4 | child one | 3 |
| 5 | child two | 3 |