MySQL 8.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

create table Category as
select 0 as id, 'Top level' as title,  NULL as parentid union all
select 1,	'Folder1',	0 union all
select 2,	'Folder2',	0 union all
select 3,	'Folder3',	0 union all
select 4,	'SubFolder1',	1 union all
select 5,	'SubFolder2',	1 union all
select 6,	'SubFolder3',	2 union all
select 7,	'SubFolder4',	2 union all
select 8,	'SubSubFolder1',	6 union all
select 9,	'SubSubFolder2',	6

Records: 10  Duplicates: 0  Warnings: 0


select *
  from Category


id
title
parentid


0
Top level
null

1
Folder1
0

2
Folder2
0

3
Folder3
0

4
SubFolder1
1

5
SubFolder2
1

6
SubFolder3
2

7
SubFolder4
2

8
SubSubFolder1
6

9
SubSubFolder2
6


with recursive cteBottomToTop
  AS (select id, parentid
        from Category
        where id = 5  -- start here
      union all
      select c.id, c.parentid
        from Category c
        inner join cteBottomToTop cte
          on c.id = cte.parentid)
select *
  from cteBottomToTop


id
parentid


5
1

1
0

0
null

id	title	parentid
0	Top level	null
1	Folder1	0
2	Folder2	0
3	Folder3	0
4	SubFolder1	1
5	SubFolder2	1
6	SubFolder3	2
7	SubFolder4	2
8	SubSubFolder1	6
9	SubSubFolder2	6