title: Retrieving parent value from mysql self join
body: <p>I have a table with fields: </p>
  
  <ul>
  <li>id</li>
  <li>name</li>
  <li>parent_id</li>
  <li>grandparent_id</li>
  </ul>
  
  <p>I want to select the id, name, parent name, grandparent name.</p>
  
  <p>Can I do this with a self join? I basically want to retrieve the "name" value where parent_id = id and return one row.</p>
  
  <p>Example:</p>
  
  <pre><code>id     name       parent_id     grandparent_id
  -----------------------------------------------
   1     Milton     NULL          NULL
   2     Year 3     1             NULL
   3     Class A    2             1
  </code></pre>
  
  <p>So i want to select the 3rd row (id = 3) but instead of returning just the parent_id and grandparent_id, i want the query to return the names of these records based on their ids. Can i create a composite field, say called parent_id_name and grandparent_id_name?</p>
  
  <p>I'm pretty sure what i am doing can be achieved by a self join or sub query, but all of the code i have tried so far has failed to work. Any help would be really appreciated.</p>