I have a CMS that is multi-lingual. There are about 300 words and phrases used throughout the system that change when the user changes the selected language. English is the default language. Other languages may not, at any given point, have been completely translated. I want the English version to be the fall back on a case by case basis where a particular word or phrase has not been translated into the selected language.
(stack overflow doesn't allow the table html tag, so I will show two records using an unordered list)
language_id label translation
en your_account Your Account
zh your_account 您的賬戶
FROM (SELECT translation as translation_result FROM languages WHERE
label='your_account' AND language_id = 'zh')A
CASE A.translation_result IS NOT NULL ELSE
(SELECT translation as translation_result FROM languages WHERE
label='your_account' AND language_id = 'en')B
Here's what I would do :
SELECT (IFNULL(ZH.translation, EN.translation)) AS translation_result FROM languages EN LEFT JOIN languages ZH ON EN.label=ZH.label AND ZH.language_id = 'zh' WHERE EN.label='your_account' AND EN.language_id = 'en'
Since english is the master language and always populated, you make your base query on the english records, which always returns something. From what the english returns, you make a
LEFT JOIN on the same table, for the same label but with diffrent language. If the
JOIN is matching something (you don't have
NULL) you use this value
But I think the query you shown is not a good example as it is not representative of what your end-queries on language table will be in the application. Normally you should make your "master" query on other table(s), and add
LEFT JOIN queries on the
language table(s) following the user's language
You can add a real example and I'll adapt my answer to match it.