title: How do I select only one generation of a hierarchical tree using an table parent child relation with SQL?
body: <p>Let's say there is a table called people with the following columns:</p>
  
  <ul>
  <li>person_id (integer)</li>
  <li>parent_person_id (integer)</li>
  <li>name (varchar)</li>
  </ul>
  
  <p>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:</p>
  
  <p>person_id, parent_person_id, name</p>
  
  <ul>
  <li>1, 1, A </li>
  <li>2, 2, B
  
  <ul>
  <li>3, 2, C
  
  <ul>
  <li>4, 3, D</li>
  </ul></li>
  <li>5, 2, E
  
  <ul>
  <li>6, 5, F
  
  <ul>
  <li>7, 6, G</li>
  <li>8, 6, H</li>
  </ul></li>
  </ul></li>
  </ul></li>
  </ul>
  
  <p>Consider on the structure above, A &amp; B as first generation, C &amp; E as second generation, D &amp; F as third generation and G &amp; H as fourth generation. For the first generation elements, the parent_person_id is equal to the person_id of the element. </p>
  
  <p>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:</p>
  
  <p>1st Generation</p>
  
  <pre><code>person name  |  parent name
  A            |  A
  B            |  B
  </code></pre>
  
  <p>2nd Generation</p>
  
  <pre><code>person name  |  parent name
  C            |  B
  E            |  B
  </code></pre>
  
  <p>3rd Generation</p>
  
  <pre><code>person name  |  parent name
  D            |  C
  F            |  E
  </code></pre>
  
  <p>4th Generation</p>
  
  <pre><code>person name  |  parent name
  G            |  F
  H            |  F
  </code></pre>
  
  <p>I would like to pass a parameter to define each generation should be listed as child on the following query. </p>
  
  <pre><code>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 :)
  ;
  </code></pre>
  
  <p>Does anyone have any ideas on how I can achieve this? Any help is welcome.</p>