Panagiotis Koursaris Panagiotis Koursaris - 3 months ago 7
MySQL Question

If one record does not exist select another

I've build an Multi Language app. The database looks like:

Table things

|id| img |url|
--------------
|1 | | |
|2 | | |
|3 | | |


Table things_translations

|id| thing_id | title | locale |
---------------------------------
|1 | 1 | | en |
|2 | 2 | | en |
|3 | 3 | | en |
|4 | 2 | | ru |


I have the default language to English (locale=en).

When I get the translations
title
from
things_translations
in other language than English and this language does not exist yet in the database I would like to get the english version of it.

I've tried the below query but returns all records instead:

select things.id
FROM things
INNER JOIN things_translations ON things.id = things_translations.thing_id
WHERE CASE WHEN things_translations.locale = ru IS NULL
THEN things_translations.locale = en END


With the above query I get both language for
thing_id:2
but I only want to get the
ru locale
not the
en
because exist.

Answer
SELECT IFNULL(TT1.title,TT.title)
FROM things T
LEFT JOIN things_translations TT 
    ON (T.id = TT.thing_id AND TT.locale = 'en')
LEFT JOIN things_translations TT1 
    ON (T.id = TT1.thing_id AND TT1.locale = 'ru')
WHERE T.id = 2;

SQL Demo