'MySQL join with conditional "on" statement
Say I have the below 2 tables:
text
id
text_lang
id
media_id
lang
text
(these are not the actual table structures - but I just have 2 tables with one containing all the localized strings that "belong" to the first table)
I want to join the tables, with a "preferred" language. So basically:
SELECT text.id, text_lang.text
FROM text
LEFT JOIN text_lang ON text.id = text_lang.text_id
AND (only a single row, and if there's a row with lang="en", pick that,
otherwise just pick any language)
The part I'm having trouble with is the "only a single row, and if there's a row with lang="en" pick that, otherwise just pick any language".
I think this does what I'm looking for:
SELECT text.id, text_lang.text
FROM text
JOIN text_lang ON text_lang.text_id = text.id AND
text_lang.lang = (select lang
FROM text_lang sub_text_lang
WHERE sub_text_lang.text_id = text.id
ORDER BY text_lang.lang = "en" DESC
limit 1)
... but I'd like to avoid a sub-query if possible, as I assume that would be quite a hit to the query performance.
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|
