title: Using a Common Table Expression (CTE) in a Parent-Child Tree relationship
body: <p>I'm making a little webapp with a Parent/Child relationship. The problem is that the relation is recursive, is not just one level.</p>
  
  <p>Let say:</p>
  
  <p>Bob is father of Peter
  Peter is father of Steve
  Steve is father of Charles</p>
  
  <p>Actually I'm doing a <code>RIGHT JOIN</code> but obviously doing that I can only access to inmmediate child.</p>
  
  <p>This is that I want to achieve:</p>
  
  <p>If the input is <code>Steve</code> I expect to get <code>[Charles]</code> as output.</p>
  
  <p>If the input is <code>Peter</code> I expect to get <code>[Steve, Charles]</code> as output.</p>
  
  <p>If the input is <code>Bob</code>   I expect to get <code>[Peter, Steve, Charles]</code> as output.</p>
  
  <p>I read that CTE are perfect for this situation, because doing something like this is weird and a bit crazy (in my opinion):</p>
  
  <pre><code>SELECT children_user_id, ud.name, parent_user_id 
  FROM
  ((SELECT * FROM users_parent
    ORDER BY parent_user_id, children_user_id) parents_sorted,
   (SELECT @pv := '1') initialisation)
  LEFT JOIN users_data ud
      ON parents_sorted.children_user_id = ud.user_id
  WHERE
      FIND_IN_SET(parent_user_id, @pv) &gt; 0 AND
      @pv := CONCAT(@pv, ',', children_user_id);
  </code></pre>
  
  <p>My table looks like something like:</p>
  
  <p><code>users_parent(parent_user_id, children_user_id)</code></p>
  
  <p>I'm completely new to CTE, how can I get all decendents/progeny of a give user, using CTE?</p>
  
  <p>Thanks for reading and please forgive my bad English.</p>