MySQL 8.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
select version();


version()


8.0.30


CREATE TABLE test (
  id INT,
  parent_id INT,
  title VARCHAR(100)
);

INSERT INTO test VALUES
(1,null,1),
(2,null,2),
(3,1,3),
(4,3,4),
(5,2,5),
(6,5,6),
(7,6,7);

Records: 7  Duplicates: 0  Warnings: 0


SELECT * FROM test;


id
parent_id
title


1
null
1

2
null
2

3
1
3

4
3
4

5
2
5

6
5
6

7
6
7


-- вывод ветки дерева
WITH RECURSIVE
cte (id, title, parent_id) AS (
  SELECT     id,
             title,
             parent_id
  FROM       test
  WHERE      id = 1
  UNION ALL
  SELECT     t.id,
             t.title,
             t.parent_id
  FROM       test t
  INNER JOIN cte
          ON t.parent_id = cte.id
)
SELECT * FROM cte;


id
title
parent_id


1
1
null

3
3
1

4
4
3


-- вывод самого верхнего родителя по дочернему узлу
WITH RECURSIVE
cte (id, title, parent_id) AS (
  SELECT     id,
             title,
             parent_id
  FROM       test
  WHERE      id = 4 -- < id узла
  UNION ALL
  SELECT     t.id,
             t.title,
             t.parent_id
  FROM       test t
  INNER JOIN cte
          ON t.id = cte.parent_id
)
SELECT * FROM cte
  WHERE parent_id IS NULL;


id
title
parent_id


1
1
null


WITH RECURSIVE
cte (id, title, parent_id) AS (
  SELECT     id,
             title,
             parent_id
  FROM       test
  WHERE      id = 6 -- < id узла
  UNION ALL
  SELECT     t.id,
             t.title,
             t.parent_id
  FROM       test t
  INNER JOIN cte
          ON t.id = cte.parent_id
)
SELECT * FROM cte
  WHERE parent_id IS NULL;


id
title
parent_id


2
2
null
id	parent_id	title
1	null	1
2	null	2
3	1	3
4	3	4
5	2	5
6	5	6
7	6	7