MySQL 8.0

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

create table t1(id int, name varchar(10),surname varchar(10),parent int)
insert into t1 values
(1,'Daniel','Brown',0),
(2,'John','Brown',5),
(3,'Andrew','Brown',4),
(4,'Simon','Smith',0),
(5,'Peter','Smith',1)
select id,name,surname,parent from t1
where surname='Brown'
union all
select a1.id,a1.name,a1.surname,a1.parent from t1 a join t1 a1
on a.parent=a1.id
where a.surname='Brown'


id
name
surname
parent


1
Daniel
Brown
0

2
John
Brown
5

3
Andrew
Brown
4

4
Simon
Smith
0

5
Peter
Smith
1

id	name	surname	parent
1	Daniel	Brown	0
2	John	Brown	5
3	Andrew	Brown	4
4	Simon	Smith	0
5	Peter	Smith	1