Mysql left join with or后备语言条件

Question

我有两个表'countries‘和'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是备用语言，所以我的目标是连接countries_lang，如果不在表中，则传递一个language，返回备用语言值

示例：

所选语言为de_DE

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

我试过了：

    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) 

但返回c.id未知列

有什么建议吗？我做测试来解决这个问题的时间是2天

感谢所有人！

编辑

我也尝试过这种方法，但总是返回备用语言的值

    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

我对您的模式逻辑是什么以及您试图查询的确切内容知之甚少。这是我能根据你提供的信息想出的。让我知道进展如何。

查询：

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

结果：

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

Answer 2

好了，找到了解决方案，希望能对某些人有所帮助！

SELECT * FROM countries AS c
LEFT JOIN ( 
    SELECT jc.id, countries_lang.pid, countries_lang.lang_code,

    COALESCE(
        (
            SELECT lang_name FROM countries_lang 
            WHERE countries_lang.pid = jc.id AND countries_lang.lang_code = 'de_DE'

        ),
        (
            SELECT lang_name FROM countries_lang 
            WHERE countries_lang.pid = jc.id AND countries_lang.lang_code = 'en_GB'

        )
    ) AS coal_lang_name,

    COALESCE(
        (
            SELECT lang_capital FROM countries_lang 
            WHERE countries_lang.pid = jc.id AND countries_lang.lang_code = 'de_DE'

        ),
        (
            SELECT lang_capital FROM countries_lang 
            WHERE countries_lang.pid = jc.id AND countries_lang.lang_code = 'en_GB'

        )
    ) AS coal_lang_capital

    FROM countries_lang

    join countries jc on jc.id = countries_lang.pid
) AS cl ON 
(c.id = cl.pid)

要应用于所有语言，只需设置2个会话变量，一个用于fallback_language，另一个用于选定的语言

示例

$fallback_lang =$_SESSION‘’en_GB‘；或任何其他$lang = $_SESSION'de_DE'；或从select获取的任何其他->

并在上述代码中进行更改

第一次合并，其中countries_lang.pid = jc.id和countries_lang.lang_code = '$lang‘

第二次合并，其中countries_lang.pid = jc.id和countries_lang.lang_code = '$fallback_lang‘

COALESCE获取第一个不为NULL的值，因此在selected之前进行回退。

干杯！！