I have so tables: enter image description here

and so data at Language table:

enter image description here

and so data at Text table: enter image description here

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)?


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

    ,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.


If code requests a text in 'ua' (ukrainian) and this is no any text for this language, so it is going to search text for russian. if found - ok, if don't it will look for a text for english. List of languages can vary.

  • What is the role of CommentId? You don't mention it in your explanation of the problem. Aug 18 '11 at 18:20
  • I presume that the CommentId is shared across multiple Comment_Text rows so that all translations of the text will have the same Comment_Id. Is that correct? (eg CommentID of 1 will say the same thing in different languages)
    – Richard
    Aug 18 '11 at 18:36
  • @Richard you are right.
    – garik
    Aug 18 '11 at 18:40

Using a recursive CTE, this query builds a table of all languages that point somewhere in their default chain to a language that has the required comment. It shows each language next to its first default that has the required comment. It then filters this table to give you the first available text for the language you selected.

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

WITH languages AS (
    -- base case: language has required comment
          lt.LanguageId AS RootLanguageId
        , lt.LanguageId
        , lt.Code
        , lt.DefaultId
        , 0 AS Level
        dbo.common_LanguageType lt
    --  lt.LanguageId = lt.DefaultId
        EXISTS (
            SELECT *
            FROM dbo.common_Text    t
                    t.CommentId = @CommentId
                AND t.LanguageId = lt.LanguageId


    -- recursive case: language is not its own default and
    --                 does not have the required comment
        , l.LanguageId
        , l.Code
        , l.DefaultId
        , l_default.Level + 1 AS Level
                    dbo.common_LanguageType l
        INNER JOIN  languages               l_default
            ON l.DefaultId = l_default.LanguageId
            l.LanguageId <> l.DefaultId
        AND NOT EXISTS (
            SELECT *
            FROM dbo.common_Text    t
                    t.CommentId = @CommentId
                AND t.LanguageId = l.LanguageId
                languages       l
    INNER JOIN  dbo.common_Text t
        ON  l.RootLanguageId = t.LanguageId
    l.Code = @LanguageCode

Try just running

FROM languages;

if you want to get a feel for what the recursive CTE is doing. I've uploaded a script to create the tables, insert some sample data, and run the above code to gist.

Pretty sexy, no?

  • This is nice. I wouldn't have thought to use COALESCE. How does CommentId play into this, though?
    – Richard
    Aug 18 '11 at 18:15
  • 1
    @Richard - I didn't understand what role it played in garik's original query so I ignored it and went off his textual description. I'll ask garik. Aug 18 '11 at 18:19
  • I have updated table schema image
    – garik
    Aug 18 '11 at 18:30
  • @garik - I've updated my answer to filter in just the appropriate comments. Aug 18 '11 at 18:46
  • 2
    I have tested it. It works and it works faster 45% again my 55%. Perfect! Thanks! I hope, you have not had time to waste! :)
    – garik
    Aug 18 '11 at 23:04

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.