skip to Main Content

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


  1. Chosen as BEST ANSWER

    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?)


  2. 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

    Login or Signup to reply.
Please signup or login to give your own answer.
Back To Top
Search