id    aName  code
------------------
1     dog     DG
2     cat     CT
3     bat     BT
4     badger  BDGR
...   ...     ...

id   orgID   isTranslated  langID   aName
----------------------------------------------
1     2           1         3       katze
2     1           1         3       hund
3     3           0         3       (NULL)
4     4           1         3       dachs
...   ...         ...       ...     ...

id Langcode
-----------
1    FR
2    CZ
3    DE
4    RU
...  ...

id    aName  code    isTranslated
---------------------------------
1     hund     DG         1
2     katze    CT         1
3     bat      BT         0
4     dachs    BDGR       1
...   ...      ...        ...

SELECT Original.id,
       CASE isTranslated
         WHEN 1 THEN Translated.aName
         ELSE        Original.aName
       END AS aName,
       code,
       isTranslated
FROM Original
JOIN Translated ON Original.id = Translated.orgID
WHERE Translated.langID = (SELECT id FROM Lang WHERE Langcode = 'DE')

SELECT Original.id, Original.code, Translated.aName
FROM Original
JOIN Lang
ON Lang.langCode = 'DE'
JOIN Translated
ON Translated.orgId = Original.id
AND Translated.langId = Lang.id
AND Translated.aName IS NOT NULL;