title: MySQL Order By Hierarchy
body: <p>I asked this question <a href="https://stackoverflow.com/questions/29655419/mysql-order-by-fields-parents" title="previously">previously</a> but this was for a two-layer tree and the solution I was given worked perfectly.</p>
  
  <p>I now have a multi level tree (up to 3, but lets assume there could be more in the future.</p>
  
  <p>My code currently looks like this:</p>
  
  <pre><code>SELECT * FROM fin_document AS finl
  
  LEFT OUTER JOIN fin_document AS finl2
  ON finl2.id = finl.parent_line_id
  
  ORDER BY
  CASE WHEN finl2.ordinal IS NULL THEN finl.ordinal
  ELSE concat(finl2.ordinal,'-',finl.ordinal) END
  </code></pre>
  
  <p>Lets assume a similar tree as before:</p>
  
  <pre><code>(id)  (Item)    (#)  (parent_line_id)
  1234 - Car    -  1 -  null
  0000 - Boat   -  2 -  null
  2222 - House  -  4 -  null
  6545 - Bike   -  5 -  null
  6547 - Wheels -  0 -  1234
  4442 - Bed    -  1 -  2222
  1474 - Sink   -  0 -  2222
  9456 - Tires  -  0 -  6547                  *New item, child of wheels
  8975 - L.Nuts -  1 -  6547                  *New item, child of wheels
  </code></pre>
  
  <p>oh and the # column is "ordinal"</p>
  
  <p>So how would I get this to sort proper with more than one parent?</p>
  
  <p>The proper sort should look like:</p>
  
  <pre><code>(id)  (Item)    (#)  (parent_line_id)
  1234 - Car    -  1 -  null
  6547 - Wheels -  0 -  1234
  9456 - Tires  -  0 -  6547
  8975 - L.Nuts -  1 -  6547 
  0000 - Boat   -  2 -  null
  2222 - House  -  4 -  null
  1474 - Sink   -  0 -  2222
  4442 - Bed    -  1 -  2222
  6545 - Bike   -  5 -  null
  </code></pre>
  
  <p>Note: I cannot alter the tables whatsoever. I am only able to pull data from the tables, as the tables are managed by another company, who's software we use. I'm aware that they will get more and more complex if there are more children, but I do not think there will be more than 3-4 children for what my company will be using this for. Unfortunately, due to this complexity, this is why I had to return here and ask again :(</p>