MariaDB 10.6

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

CREATE TABLE `person` (
  `ID` varchar(10) NOT NULL,
  `PARENT` varchar(10) NOT NULL,
  `TYPE` varchar(10) NOT NULL,
  `NAME` varchar(50) NOT NULL
)
INSERT INTO `person` (`ID`, `PARENT`, `TYPE`, `NAME`) VALUES
('1000000001', '1000000001', 'A', 'first'),
('1000000002', '1000000004', 'B', 'second'),
('1000000003', '1000000002', 'C', 'third'),
('1000000004', '1000000002', 'C', 'fourth'),
('1000000005', '1000000004', 'C', 'fifth'),
('1000000006', '1000000002', 'C', '6th'),
('1000000007', '1000000002', 'C', '7th'),
('1000000008', '1000000002', 'C', '8th'),
('1000000009', '1000000002', 'C', '9th'),
('1000000010', '1000000005', 'D', '10th'),
('1000000011', '1000000005', 'D', '11th'),
('1000000012', '1000000005', 'D', '12nd'),
('1000000013', '1000000005', 'D', '13rd')
;

Records: 13  Duplicates: 0  Warnings: 0


WITH
  RECURSIVE
    Children
AS
(
  SELECT * FROM `person` WHERE ID = '1000000002'
  UNION
  SELECT m.* FROM `person` as m INNER JOIN Children t on m.PARENT = t.ID
)
SELECT * FROM Children 


ID
PARENT
TYPE
NAME


1000000002
1000000004
B
second

1000000003
1000000002
C
third

1000000004
1000000002
C
fourth

1000000006
1000000002
C
6th

1000000007
1000000002
C
7th

1000000008
1000000002
C
8th

1000000009
1000000002
C
9th

1000000005
1000000004
C
fifth

1000000010
1000000005
D
10th

1000000011
1000000005
D
11th

1000000012
1000000005
D
12nd

1000000013
1000000005
D
13rd

ID	PARENT	TYPE	NAME
1000000002	1000000004	B	second
1000000003	1000000002	C	third
1000000004	1000000002	C	fourth
1000000006	1000000002	C	6th
1000000007	1000000002	C	7th
1000000008	1000000002	C	8th
1000000009	1000000002	C	9th
1000000005	1000000004	C	fifth
1000000010	1000000005	D	10th
1000000011	1000000005	D	11th
1000000012	1000000005	D	12nd
1000000013	1000000005	D	13rd