MySQL 8.0

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

Create Table categories(id INT, parentId INT, name VARCHAR(50), path VARCHAR(50), isLastLevel INT);

Insert Into categories Values
(1,0,'Cat 1','Cat 1',0),
(2,1,'Cat 2','Cat 1 > Cat 2',0),
(3,2,'Cat 3','Cat 1 > Cat 2 > Cat 3',1),
(4,0,'Cat A','Cat A',0),
(5,4,'Cat B','Cat A > Cat B',1);

Records: 5  Duplicates: 0  Warnings: 0


with recursive cte (id, name, parentId, path) AS 
(
  select id, name, parentId, path
  from  categories
  where id = 2
  union all
  select c.id, c.name, c.parentId, c.path
  from categories c join cte 
  on cte.parentId = c.id 
),
cte2 (id, name, parentId, path) AS 
(
  select id, name, parentId, path
  from  categories
  where id = 2
  union all
  select c.id, c.name, c.parentId, c.path
  from categories c join cte 
  on c.parentId = cte.id 
)
select * from cte union select * from cte2
order by id;


id
name
parentId
path


1
Cat 1
0
Cat 1

2
Cat 2
1
Cat 1 > Cat 2

3
Cat 3
2
Cat 1 > Cat 2 > Cat 3


SELECT C1.id, C1.parentId, C1.name, C1.path
FROM categories C1 JOIN categories C2
ON C1.path LIKE CONCAT('%', C2.name, '%') OR C2.path LIKE CONCAT('%', C1.name, '%')
WHERE C2.id=2
ORDER BY C1.id


id
parentId
name
path


1
0
Cat 1
Cat 1

2
1
Cat 2
Cat 1 > Cat 2

3
2
Cat 3
Cat 1 > Cat 2 > Cat 3

id	name	parentId	path
1	Cat 1	0	Cat 1
2	Cat 2	1	Cat 1 > Cat 2
3	Cat 3	2	Cat 1 > Cat 2 > Cat 3