MySQL 8.0

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})
  ';
  set @sql = replace(@sql, '{list}', @list);
  prepare stmt from @sql;
  execute stmt;
end
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