Mysql left join with or condition for fallback language

Question

I have 2 table 'countries' and 'countries_lang'

+------+--------------+-----------------+
| id   | region_code  | sub_region_code |
+------+--------------+-----------------+
|    1 | 142          | 034             |
|    2 | 150          | 154             |
|    3 | 002          | 015             |
+------+--------------+-----------------+

+-----+--------------+---------------+--------------+
| id  | pid  | lang_code | lang_name | lang_capital |
+-----+------------------+-----------+--------------+
|   1 | 1    | en_GB     |Canada     | Ottawa       |
|   2 | 1    | de_DE     |Kanada     | Ottawa       |
|   3 | 2    | en_GB     |Italy      | Rome         |
+-----+------+-----------+-----------+--------------+

en_GB is the fallback language so my goal is to join countries_lang passing a lnaguage if not in table return the fallback language value

example:

selected language is de_DE

+------+--------------+-----------------+--------------+
| id   | region_code  | lang_name       | lang_capital |
+------+--------------+-----------------+--------------+
|    1 | 142          | Kanada          | Ottawa       +
|    3 | 150          | Italy           | Rome         + (fallback language)
+------+--------------+-----------------+--------------+

I tried:

    Select * FROM countries AS c 
     LEFT JOIN ( 
        SELECT pid, 
            COALESCE(
                (
                    SELECT lang_name FROM countries_lang WHERE lang_code = 'de_DE' AND pid = c.id
                ),
                (
                    SELECT lang_name FROM countries_lang WHERE lang_code = 'en_GB' AND pid = c.id

                )
            ) AS cl_name,
            COALESCE(
                (
                    SELECT lang_capital FROM countries_lang WHERE lang_code = 'de_DE' AND pid = c.id
                ),
                (
                    SELECT lang_capital FROM countries_lang WHERE lang_code = 'en_GB' AND pid = c.id

                )
            ) AS cl_capital
        FROM countries_lang
    ) AS cl 

    ON (c.id = cl.pid) 

but return c.id UNKNOWN Column

Any suggestion ? are 2 days that I am making test to solve this problem

Thanks to all !!!

EDIT

I tried in this way too but return always the fallback languages value

    Select * FROM countries AS c
    LEFT JOIN ( 
        SELECT pid, lang_name, lang_code FROM countries_lang WHERE lang_code = 'de_DE' AND lang_code IS NOT NULL OR lang_code = 'en_GB'
    ) AS cl ON 
    c.id = cl.pid

Answer 1

I have limited knowledge of what your schema logic is and what exactly you are trying to query. Here is what I can come up with based on your given information. Let me know how it goes.

Query:

select cl.id, c.region_code, cl.lang_name, cl.lang_capital
from countries_lang cl 
join countries c on c.id = cl.pid
left join 
(select cl.id, cl.pid
from countries_lang cl 
join countries c on c.id = cl.pid and cl.lang_code = 'de_DE') fcl on cl.pid = fcl.pid 
where cl.id = fcl.id or fcl.id is null

Result:

+------+--------------+-----------------+--------------+
| id   | region_code  | lang_name       | lang_capital |
+------+--------------+-----------------+--------------+
|    2 | 142          | Kanada          | Ottawa       +
|    3 | 150          | Italy           | Rome         + 
+------+--------------+-----------------+--------------+