SQL Database view where non-localized "default" values are shown as localized for each language

Question

I have 3 tables: Language, TextBlock, and TextBlocki18n. By default, the TextBlock table uses en-US for the content column. I'm looking for help writing a view that will show the en-US version of the Content column with each language code for any content that isn't translated.

We currently have 12 languages and will be adding at least 12 more. I'm only demonstrating with 3:

Language:

LangCode | LangName
en-US    | English (US)
fr-FR    | French (France)
es-ES    | Spanish (Spain)

TextBlock:

ID | Content
1  | One
2  | Two
3  | Three

TextBlocki18n

TbFK | LangFK | Content
1    | fr-CA  | Une
1    | es-ES  | Uno
2    | es-ES  | Dos
3    | fr-CA  | Trois

I would like to create a view that will show the "default" value of the TextBlock.Content field (if it isn't translated in the TextBlocki18n table)...but for each language available. Here is the desired output if I select all from the view:

TbID | LangCode | Content
1    | en-US    | One
2    | en-US    | Two
3    | en-US    | Three
1    | fr-CA    | Une
2    | fr-CA    | Two
3    | fr-CA    | Trois
1    | es-ES    | Uno
2    | es-ES    | Dos
3    | es-ES    | Three

The idea being that I can run a query like:

SELECT TbID, Content FROM dbo.ViewName WHERE LangCode = 'es-ES';

TbID | Content
1    | Uno
2    | Dos
3    | Three

As soon as we translate the record for "Three", the view would return that value instead of the default

I know I can do a UNION for each language as such:

SELECT
    ID as TbID
    ,'en-US' as LangCode
    ,Content
FROM TextBlock
UNION
SELECT
    tb.ID
    ,'fr-FR'
    ,COALESCE(tbi18n.Content, tb.Content)
FROM TextBlock AS tb
LEFT JOIN TextBlocki18n AS tbi18n ON 
    tb.ID = tbi18n.TbFK
    AND tbi18n.LangFK = 'fr-FR'
UNION
SELECT
    tb.ID
    ,'es-ES'
    ,COALESCE(tbi18n.Content, tb.Content)
FROM TextBlock AS tb
LEFT JOIN TextBlocki18n AS tbi18n ON 
    tb.ID = tbi18n.TbFK
    AND tbi18n.LangFK = 'es-ES'

I'm certain there's an approach that doesn't require all of the UNIONS... especially as we continue to add more languages to our system and have translations. I just can't seem to come up with a more elegant solution. I could always use a stored procedure but that feels like defeat.

Answer 1

Why not just

select tb.ID, l.LangCode, ISNULL(tbi18n.Content, tb.Content), CASE WHEN tbi18n.ID IS NULL THEN 1 ELSE 0 END AS translationMissing
from Languages l
CROSS JOIN TextBlock tb
LEFT JOIN TextBlocki18n AS tbi18n
  ON tbi18n.TbFK = tb.ID
  AND tbi18n.LangFK = l.LangCode

This fetches all language + textblock combos, and then fetches language specific translation if it exists, otherwise it falls back to english