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.

CodePudding user response：

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

CodePudding user response：

I managed to come up with the following solution, which does not use JOIN:

SELECT DISTINCT contentId as id, 
CASE
    WHEN (SELECT language FROM content WHERE contentId = id AND language = 'de') = 'de' THEN (SELECT text FROM content WHERE contentId = id AND language = 'de')
    ELSE (SELECT text FROM content WHERE contentId = id AND language = 'en')
END as text
FROM content

It looks like a valid alternative to the above answer (Probably less efficient?)