How do I select only one generation of a hierarchical tree using an table parent child relation with SQL?

Question

Let's say there is a table called people with the following columns:

• person_id (integer)
• parent_person_id (integer)
• name (varchar)

Now, let's say the table has being populated and the name values are just letters (A, B, C, D ...). Considering the parents of each we end up with a hierarchical tree like the one bellow. The values are on the format:

person_id, parent_person_id, name

• 1, 1, A
• 2, 2, B
  • 3, 2, C
    • 4, 3, D
  • 5, 2, E
    • 6, 5, F
      • 7, 6, G
      • 8, 6, H

Consider on the structure above, A & B as first generation, C & E as second generation, D & F as third generation and G & H as fourth generation. For the first generation elements, the parent_person_id is equal to the person_id of the element.

I need to write a query that allows me to select the names from a certain generation(first, second, third, fourth, etc) of the tree. As a result I could get a table with the names of the user from a certain generation. For example:

1st Generation

person name  |  parent name
A            |  A
B            |  B

2nd Generation

person name  |  parent name
C            |  B
E            |  B

3rd Generation

person name  |  parent name
D            |  C
F            |  E

4th Generation

person name  |  parent name
G            |  F
H            |  F

I would like to pass a parameter to define each generation should be listed as child on the following query.

SELECT
    child.name as 'person name',
    parent.name as 'parent name'
FROM
    people as child
JOIN 
    people as parent
ON 
    child.parent_person_id = parent.person_id
WHERE
    -- I NEED HELP HERE :)
;

Does anyone have any ideas on how I can achieve this? Any help is welcome.

Answer 1

This is verbose and ugly and will be slow, and it's limited to 4 generations, but I don't know how else it can be done.

SELECT person_name, parent_name FROM

    (SELECT child1.name AS person_name, parent1.name AS parent_name, '1' AS generation
    FROM people as child1
    JOIN people as parent1
    ON child1.parent_person_id = parent1.person_id AND child1.parent_person_id = child1.person_id

    UNION

    SELECT child2.name AS person_name, parent2.name AS parent_name, '2' AS generation
    FROM people as child2
    JOIN people as parent2
    ON child2.parent_person_id = parent2.person_id AND child2.parent_person_id <> child2.person_id AND parent2.parent_person_id = parent2.person_id

    UNION

    SELECT child3.name AS person_name, parent3.name AS parent_name, '3' AS generation
    FROM people as child3
    JOIN people as parent3
    ON child3.parent_person_id = parent3.person_id AND parent3.parent_person_id <> parent3.person_id
    JOIN people as grandparent1
    ON parent3.parent_person_id = grandparent1.person_id AND grandparent1.parent_person_id = grandparent1.person_id

    UNION

    SELECT child4.name AS person_name, parent4.name AS parent_name, '4' AS generation
    FROM people as child4
    JOIN people as parent4
    ON child4.parent_person_id = parent4.person_id AND parent4.parent_person_id <> parent4.person_id
    JOIN people as grandparent2
    ON parent4.parent_person_id = grandparent2.person_id AND grandparent2.parent_person_id <> grandparent2.person_id
    JOIN people as greatgrandparent
    ON grandparent2.parent_person_id = greatgrandparent.person_id AND greatgrandparent.parent_person_id = greatgrandparent.person_id
    ) AS tree

WHERE generation = ?