Select rows WHERE any row in column is equal to 1 or if not, column is equal to 2, but not both

Question

I have got a translation table for my text like:

Table: todos

day | text_id
-------------
 0  | 1
 1  | 2
 1  | 1

Table: translations

lang | text_id | text
---------------------
 deu | 1       | Laufen
 eng | 1       | Running
 eng | 2       | Swimming

Now I want to lookup my todos in German (deu). My Problem is, I don´t have the translation (e.g.) for text_id 2: Swimming in German.

My default query would be:

SELECT todos.day, translations.text
  INNER Join translations
  ON todos.text_id = translations.text_id
  WHERE translations.locale = 'deu';

I would get:

day | text
--------------
 0  | Laufen
 1  | Laufen

But I want:

day | text
--------------
 0  | Laufen
 1  | Swimming
 1  | Laufen

How can I get some missing rows? First I should get all needed rows with:

SELECT todos.day, translations.text
  INNER Join translations
  ON todos.text_id = translations.text_id
  WHERE translations.locale = 'deu' or translations.locale = 'eng';

And then remove all 'eng' which are duplications but - How?

Sorry for this terrible title, I don´t know how to describe it properly ...

Answer 1

You need left join to keep all the records in the first table. Then you need it twice to get the English records for the default:

SELECT td.day, coalesce(tdeu.text, teng.text) as text
FROM todos td left join
     translations tdeu
     ON td.text_id = tdeu.text_id and tdeu.locale = 'deu' left join
     translations teng
     ON td.text_id = teng.text_id and teng.locale = 'eng';