MySQL join with conditional "on" statement-CodePudding

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.

CodePudding user response：

I can think of a few ways but since you're trying to avoid subquery, perhaps you can do something like this:

SELECT t.id,
       SUBSTRING_INDEX(
          GROUP_CONCAT(text ORDER BY CASE WHEN lang='en' THEN 0 ELSE RAND() END),',',1) 
          AS extracted_text
       FROM
       text t 
       LEFT JOIN text_lang tl ON tl.text_id=t.id
 GROUP BY t.id;

Use GROUP_CONCAT() with a customized ORDER BY then use SUBTRING_INDEX() to get the first value using comma (the default GROUP_CONCAT() separator) as the delimiter.

I've made a fiddle with sample data here: https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=0d0501f36af1ba4e181856f761dbd5f7 . You can run the fiddle a few times to see that the text value other than 'en' change.