CODEWITHSUNDEEP
sqljoincase
raven_977
raven_977

Reputation: 475

SQL left join with case and limitied conditions

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

Upvotes: 0

Views: 60

Answers (2)

tinazmu
tinazmu

Reputation: 5139

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.

Upvotes: 2

Richard Deeming
Richard Deeming

Reputation: 31198

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
;

Upvotes: 1

Related Questions