MySQL tree structure

Question

Hello i am trying to prepare tree structure with MySql, tables look something like this.

 |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  |

My Question is:

1. Is it possible to do left join and sort columns by name, but if in lang is eq to "fra" return that row with that name, otherwise return "eng" name.

Pseudo 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

So final result will be something like this, in total "fra" lang has priority, and all result should be sorted by name.

        |      id| name  |lang  |
        |--------|-------|------|
        |       1| BCD   | fra  |
        |       2| HIG   | fra  |
        |       3| WYX   | eng  |

Answer 1

this should work, give it a try:

SELECT 
   id, 
   name,
   CASE WHEN tfra.entry_id is null THEN teng.name ELSE tfra.name END as name 
FROM table 
LEFT JOIN table2 tfra ON id = tfra.entity_id AND tfra.lang='fra'
LEFT JOIN table2 teng ON id = teng.entity_id AND teng.lang='eng'
WHERE (IF lang = 'fra' return french name otherwise return just english name) 
ORDER BY name ASC