SQL - Fallback to default language when translate does not exist

Question

Recording texts in different languages in a table (tbl_i18n) with the following structure:

text_FK | language_FK | value
-----------------------------
    1   |       1     | hello
    1   |       2     | hallo
    2   |       1     | world
    3   |       1     | test

gives texts of a specific language (id = 2) with a simple join like:

SELECT [value] FROM tbl_i18n i
   JOIN tbl_products p ON p.text_id = i.text_FK
   JOIN tbl_languages l ON l.id = i.language_FK AND i.language FK = 2;

and the result is:

 value
-------
 hallo

How could aforementioned select statement changed so we could have got a default language and when translate for a text fields does not exist their fallback text will be shown and the result will became:

 value
-------
 hallo
 world
 test

Also check this question to see if it helps https://stackoverflow.com/questions/3238651/retrieving-i18n-data-with-fallback-language?rq=1 — irfandar, Sep 14 '17 at 09:04
@lostmylogin, here we've got 3 textfields and 4 records. 3 in english and 1 in german. hello field has a german translation but others dont. i want to get all fields in german but when there is no german translation english fields should be returned. Tanx for the link; looks promising. — dNitro, Sep 14 '17 at 10:50

score 2 · Accepted Answer · answered Sep 14 '17 at 09:52

2

LEFT JOIN the language table twice. The first time for wanted language, the second time for fallback value. Use COALESCE to pick wanted language if available, otherwise fallback language.

SELECT coalesce(l1.[value], l2.[value])
FROM tbl_i18n i
JOIN tbl_products p ON p.text_id = i.text_FK
LEFT JOIN tbl_languages l1 ON l.id = i.language_FK AND i.language_FK = 2
LEFT JOIN tbl_languages l2 ON l.id = i.language_FK AND i.language_FK = 1;

answered Sep 14 '17 at 09:52

jarlh

42,561
8
45
63

Brief and precise. Tanx. – dNitro Sep 14 '17 at 10:55
Is it possible to implement the approach without need to use COALESCE and to have only one language set alias, not l1, l2? Probably by using UNION.. I need to write universal method for different entities with selector parameters using Linq-to-SQL which sorts table data by localized text in other table. – Alexander Sep 10 '21 at 21:09

score 0 · Answer 2 · answered Sep 14 '17 at 09:04

0

I think in simple english you want the highest available language_FK for each text_FK.

WITH X AS (
SELECT *
     ,ROW_NUMBER() OVER (PARTITION BY text_FK ORDER BY language_FK DESC)rn
FROM TableName 
)
SELECT *
FROM X
WHERE X.rn = 1

answered Sep 14 '17 at 09:04

M.Ali

67,945
13
101
127

Here i've just used `int` ids for simplicity; Actually we use `uniqueidentifier` as ids; so i am not sure we could use `ORDER BY`. – dNitro Sep 14 '17 at 09:18

SQL - Fallback to default language when translate does not exist

2 Answers2