I have the following SQL table called ‘content’:
language | contentId | text |
---|---|---|
en | 1 | Hello |
de | 1 | Hallo |
en | 2 | World |
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:
contentId | text |
---|---|
1 | Hallo |
2 | World |
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.
2
Answers
I managed to come up with the following solution, which does not use
JOIN
:It looks like a valid alternative to the above answer (Probably less efficient?)
This is a
LEFT JOIN
which pulls always a value with ‘de’ language if not null, otherwise it gets the row with the samecontentId
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 samecontentId
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