Get names of parents recursively

Question

I have a database that looks like this:

CREATE TABLE Persons (
    id int,
    parentID int,
    name varchar(255)
);

INSERT INTO Persons (id, parentID, name) VALUES ('1', '0', 'smith');
INSERT INTO Persons (id, parentID, name) VALUES ('2', '1', 'johnson');
INSERT INTO Persons (id, parentID, name) VALUES ('3', '2', 'spencer');
INSERT INTO Persons (id, parentID, name) VALUES ('4', '3', 'duke');

I want to fetch the persons name, and name of their parent and put that in an array. Then loop through the array recursively to get an output that looks similar to this:

smith
johnson (smith)
spencer (johnson, smith)
duke (spencer, johnson, smith)

I want to do this in php and sql.

I am unsure of the sql query to use, should i use an recursive CTE? Also how should i loop through it to get the output that i want?

Please tag your question with the RDBMS that you are using: mysql, sql-server, oracle...? — GMB, Nov 11 '19 at 20:10
Possible duplicate of [Getting all parent rows in one SQL query](https://stackoverflow.com/questions/2441821/getting-all-parent-rows-in-one-sql-query) — Patrick Q, Nov 11 '19 at 20:23

score 2 · Answer 1 · answered Nov 11 '19 at 20:42

In MySQL 8.0, you can make use of a recursive common table expression:

with recursive cte as (
    select 
        id,
        parentID,
        name, 
        cast('' as char(500)) parents
        from Persons
    where parentID = 0
    union all
    select 
        p.id,
        p.parentID,
        p.name,
        concat(c.parents, case when c.parents <> '' then ',' else '' end, c.name) parents
     from Persons p
     inner join cte c on c.id = p.parentID
)
select name, parents from cte

The query starts from the root of the tree (where parentID = 0), and then walks through the hierarchy, concatenating the inheritance chain in new column parents.

Demo on DB Fiddle:

name    | parents              
:------ | :--------------------
smith   |                      
johnson | smith                
spencer | smith,johnson        
duke    | smith,johnson,spencer

Get names of parents recursively

1 Answers1

Linked