title: How to query graph/hierarchical data in mysql
body: <p>Suppose I have a table of objects structured in a hierarchy:</p>
  
  <pre><code>A
  |--B
  |--C
  |  +--D
  +--E
  </code></pre>
  
  <p>They are stored in a "parent-child" table thus:</p>
  
  <pre><code>parent  child
  A       B
  A       C
  C       D
  A       E
  </code></pre>
  
  <p>How do I query this to get the structure defined above? I think I need something that produces info like this:</p>
  
  <pre><code>object  full_path
  A       NULL
  B       A
  C       A
  D       A.C
  E       A
  </code></pre>
  
  <p>I cannot figure out how to do the objects nested more than one level deep. It feels like I might need to iterate over the table (no idea if this is possible in SQL), or otherwise use some kind of query I've never encountered before.</p>
  
  <p>Additional Info:</p>
  
  <ol>
  <li>A need not be the only orphan object.</li>
  <li>Children may have multiple parents BUT for now I'm happy with an answer to whichever scenario is easier to solve. I can abide converting a multi-parent structure to a single parent with a simple <code>GROUP BY</code> and <code>MIN</code> statement.</li>
  </ol>