'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