How to select only one generation of a hierarchical tree using the parent child relation of a table 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 that the table is populated, and the values ​​of the names are just letters (A, B, C, D ...). Looking at each parent, we end up with a hierarchical tree similar to one. Values ​​are in 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 the above structure of A and B as the first generation, C and E as the second generation, D and F as the third generation, and G and H as the fourth generation. For first-generation elements, parent_person_id is equal to person_id of the element.

, (, , , ..) . . :

1st Generation

person name  |  parent name
A            |  A
B            |  B

person name  |  parent name
C            |  B
E            |  B

3-

person name  |  parent name
D            |  C
F            |  E

4-

person name  |  parent name
G            |  F
H            |  F

, , .

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 :)
;

- , ? .

Answer 1

, 4 , , .

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 = ?