How to return text for default language

Question

I have so tables:

and so data at Language table:

and so data at Text table:

I have to return text for requested language if it exists and text for default language if it does not exist. Is it possible to do that in one query (no while, please)?

Code:

DECLARE @CommentId  bigint = 1
--DECLARE @LanguageCode  nvarchar(2) = 'en' -- "english text" returns
DECLARE @LanguageCode  nvarchar(2) = 'ua'   -- nothing at this moment

SELECT
     t.CommentId
    ,t.TextId
    ,t.[Text]
    ,t.LanguageId
    ,RequestedLanguageId = @LanguageCode
FROM dbo.common_Text t 
    INNER JOIN dbo.common_LanguageType l 
        ON t.LanguageId = l.LanguageId
WHERE l.Code = @LanguageCode 
    AND t.CommentId = @CommentId

Thank you.

Answer 1

I assume that something is messed up in the data you supplied. Didn't you mean to show a row in the text table with LanguageId = 2? Without using a recursive query or loop, you can't keep following the DefaultId of the language until you end up at English. Assuming there is a row in the text table for ukrainian's backup (2 = russian):

DECLARE 
    @CommentId BIGINT = 1,
    @LanguageCode NVARCHAR(2) = 'ua';

SELECT
    CommentId           = COALESCE(t.CommentId,  a.CommentId),
    TextId              = COALESCE(t.TextId,     a.TextId),
    [Text]              = COALESCE(t.[Text],     a.[Text]), 
    LanguageId          = COALESCE(t.LanguageId, a.LanguageId),
    RequestedLanguageId = @LanguageCode
FROM 
    dbo.common_LanguageType AS l
LEFT OUTER JOIN 
    dbo.common_Text AS t
    ON l.LanguageId = t.LanguageId
    AND t.CommentID = @CommentId
LEFT OUTER JOIN 
    dbo.common_Text AS a -- a for "alternate"
    ON l.DefaultId = a.LanguageId
WHERE
    l.Code = @LanguageCode
    AND a.CommentID = @CommentId;

If this is not the case, you need to make the question more clear. If you have LanguageId 4, 'central ukrainian' with a DefualtId = 3, when that language is requested is the query supposed to check the text table for 4, when it's not found, it checks 4's default (3), when that's not found, it checks 3's default (2), when that's not found, it checks 2's default (1) and finally returns the row for 1? If this is the case you will certainly need a more complicated query (using either a recursive CTE or a loop).

Also for the language code you should probably use NCHAR(2) as opposed to NVARCHAR(2). I hope the column is not nullable and unique.

Answer 2

Solution was found on Database Administrators site