SQL left join with case and limitied conditions-CodePudding

I want to to left join the translations table to the table1 depending on this conditions: If there is an item for the browser languange in the translation table choose this one. If not check if there is an item for the operating system language, if not choose the default (null) language.

table1

id	item
0	tax
1	fee

translation

id	item	language	text
0	tax	NULL	Steuer
1	tax	de	Steuer
2	tax	en	tax
3	fee	NULL	Gebühr
4	fee	de	Gebühr
5	fee	en	charge

SELECT * FROM table1 t1
LEFT JOIN translation t2 ON t1.item = t2.item;

will give me all items like:

results

id	item	id	item	language	text
0	tax	3	tax	NULL	Gebühr
0	tax	4	tax	de	Gebühr
0	tax	5	tax	en	charge
1	fee	0	fee	NULL	Steuer
1	fee	1	fee	de	Steuer
1	fee	2	fee	en	tax

My approach was to use CASE but the query:

DECLARE @uiLanguage VARCHAR(2) = 'en';
DECLARE @osLanguage VARCHAR(2) = 'de';

    SELECT
        *
    FROM
        item t1
        LEFT JOIN translation t2 ON t1.item = t2.item
    WHERE
        t2.language = (CASE
            WHEN t2.language = @uiLanguage THEN @uiLanguage
           ELSE @osLanguage
        END);

gives me this result:

results

id	item	id	item	language	text
0	tax	4	tax	de	Gebühr
0	tax	5	tax	en	charge
1	fee	1	fee	de	Steuer
1	fee	2	fee	en	tax

and this is what it should be or is needed:

results

id	item	id	item	language	text
0	tax	5	tax	en	charge
1	fee	2	fee	en	tax

CodePudding user response：

You can do it with joining the translation table multiple times, once for each language; in SqlServer:

DECLARE @uiLanguage VARCHAR(2) = 'en';
DECLARE @osLanguage VARCHAR(2) = 'de';

    SELECT
        t1.*,
        coalesce(t2_bl.text, t2_ol.text, t2_nl.text) as text
    FROM
        item t1
        LEFT JOIN translation t2_bl ON t1.item = t2_bl.item AND t2_bl.language = @uiLanguage
        LEFT JOIN translation t2_ol ON t1.item = t2_ol.item AND t2_ol.language = @osLanguage
        LEFT JOIN translation t2_nl ON t1.item = t2_nl.item and t2_nl.language is NULL
;

coalesce takes the first 'text' value that is not 'null'. This can be performed with case-when if coalesce function is not available.

CodePudding user response：

I suspect you'll need to use a correlated sub-query.

For example, in MS SQL Server:

DECLARE @uiLanguage VARCHAR(2) = 'en';
DECLARE @osLanguage VARCHAR(2) = 'de';

SELECT
    id,
    IsNull(t2.text, t1.text) text
FROM
    item t1
    OUTER APPLY
    (
        SELECT TOP 1 text
        FROM translation t2
        WHERE t2.item = t1.item
        AND t2.language IN (@uiLanguage, @osLanguage)
        ORDER BY CASE t2.language WHEN @uiLanguage THEN 0 ELSE 1 END
    ) t2
;