title: MySQL tree structure
body: <p>Hello i am trying to prepare tree structure with MySql, tables look something like this.</p>
  <pre><code> |id   |parent_id |               |entry_id| name  |lang  |
   |-----|----------|               |--------|-------|------|
   |   1 |    0     |               |       1| ABC   | eng  |
   |   2 |    1     |               |       1| BCD   | fra  |
   |   3 |    2     |               |       2| EFG   | eng  |
   |   4 |    2     |               |       2| HIJ   | fra  |
   |   5 |    2     |               |       3| WYX   | eng  |
  </code></pre>
  <p>My Question is:</p>
  <ol>
  <li>Is it possible to do left join and sort columns by name, but if in lang is eq to &quot;fra&quot; return  that row with that name, otherwise return &quot;eng&quot; name.</li>
  </ol>
  <p>Pseudo code</p>
  <pre><code>SELECT id, name FROM table LEFT JOIN table2 ON id = entity_id 
  WHERE (IF lang = 'fra' return french name otherwise return just english name) GROUP BY entry_id ORDER BY name ASC
  </code></pre>
  <p>So final result will be something like this, in total &quot;fra&quot; lang has priority, and all result should be sorted by name.</p>
  <pre><code>        |      id| name  |lang  |
          |--------|-------|------|
          |       1| BCD   | fra  |
          |       2| HIG   | fra  |
          |       3| WYX   | eng  |
  </code></pre>