Multilingual page – Get row with language='de' if exists else get row with language='en' from MySQL DB

I have the following SQL table called ‘content’:

My goal is to query all rows which match language=’de’.

But if a row with language=’en’ exists which has no counter part with language=’de’, I want to include it in the query result.

My desired output would be:

This problem came up when designing a multilingual page. My query should prevent that no text is shown on the ‘de’ page but rather the default ‘en’ text.

I tried to solve the issue with the CASE and EXISTS statement but was not successful.

>Solution :

SELECT en.contentId, IFNULL(de.text,en.text) AS thetext
FROM content en
LEFT JOIN content de ON en.contentId=de.contentId AND de.language='de'
WHERE en.language='en'

This is a LEFT JOIN which pulls always a value with ‘de’ language if not null, otherwise it gets the row with the same contentId but with ‘en’ language.

Or better explained, it assumes that all values of contentId have a row with ‘en’ language, and it will join each of them with a row with the same contentId but in ‘de’, if it exists.
And if it exists it will pull the text from ‘de’, otherwise from ‘en’.

Let me know if it works and it is clear enough.
Otherwise you should get familiar with LEFT JOINs https://www.w3schools.com/sql/sql_join_left.asp