MySQL 5.7

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

CREATE TABLE test (id INT, name VARCHAR(255), parent_id INT);
INSERT INTO test VALUES
(1,	'mike',	0),
(2,	'jeff',	0),
(3,	'bill',	2),
(4,	'sara',	1),
(5,	'sam',	4),
(6,	'shai',	5);
SELECT * FROM test;

Records: 6  Duplicates: 0  Warnings: 0




id
name
parent_id


1
mike
0

2
jeff
0

3
bill
2

4
sara
1

5
sam
4

6
shai
5


CREATE FUNCTION get_most_parent (id INT)
RETURNS CHAR(255) 
BEGIN
DECLARE parent_name VARCHAR(255);
    REPEAT
        SELECT name, parent_id 
        INTO parent_name, id
        FROM test
        WHERE test.id = id;
    UNTIL NOT id END REPEAT;
RETURN parent_name;
END
SELECT test.*, get_most_parent(id) TopParentName 
FROM test
WHERE id IN (3, 6);


id
name
parent_id
TopParentName


3
bill
2
jeff

6
shai
5
mike