Select all records, join with table A if join exists, table B if not

So here's my scenario:

I'm working on Localization for a project of mine, and typically I would go about doing this in the C# code, however I want to do this in SQL a bit more since I am trying to buff up my SQL a bit.

Environment: SQL Server 2014 Standard, C# (.NET 4.5.1)

Note: the programming language itself should be irrelevant, I'm only including it for completeness.

So I sort-of accomplished what I wanted, but not to the extent I wanted. It's been a while (at least a year) since I have done any SQL JOINs except basic ones, and this is quite a complex JOIN.

Here is a diagramme of the relevant tables of the database. (There are plenty more, but not necessary for this portion.)

All relationships described in the image are complete in the database - the PK and FK constraints are all setup and operating. None of the columns described are nullable. All the tables have the schema dbo.

Now, I have a query which almost does what I want: that is, given ANY Id of SupportCategories and ANY Id of Languages, it will return either:

If there is a right-proper translation for that language for that string (I.e. StringKeyId -> StringKeys.Id exists, and in LanguageStringTranslations StringKeyId, LanguageId, and StringTranslationId combination exists, then it loads StringTranslations.Text for that StringTranslationId.

If the LanguageStringTranslations StringKeyId, LanguageId, and StringTranslationId combination did NOT exist, then it loads the StringKeys.Name value. The Languages.Id is a given integer.

My query, be it a mess, is as follows:

`SELECT CASE WHEN T.x IS NOT NULL THEN T.x ELSE (SELECT
CASE WHEN dbo.StringTranslations.Text IS NULL THEN dbo.StringKeys.Name ELSE dbo.StringTranslations.Text END AS Result
FROM dbo.SupportCategories
INNER JOIN dbo.StringKeys
ON dbo.SupportCategories.StringKeyId = dbo.StringKeys.Id
INNER JOIN dbo.LanguageStringTranslations
ON dbo.StringKeys.Id = dbo.LanguageSt

Here is the first approach I came up with:

DECLARE @ChosenLanguage INT = 48;

SELECT sc.Id, Result = MAX(COALESCE(
   CASE WHEN lst.LanguageId = @ChosenLanguage      THEN st.Text END,
   CASE WHEN lst.LanguageId = sk.DefaultLanguageId THEN st.Text END)
)
FROM dbo.SupportCategories AS sc
INNER JOIN dbo.StringKeys AS sk
  ON sc.StringKeyId = sk.Id
LEFT OUTER JOIN dbo.LanguageStringTranslations AS lst
  ON sk.Id = lst.StringKeyId
  AND lst.LanguageId IN (sk.DefaultLanguageId, @ChosenLanguage)
LEFT OUTER JOIN dbo.StringTranslations AS st
  ON st.Id = lst.StringTranslationId
  --WHERE sc.Id = 1
  GROUP BY sc.Id
  ORDER BY sc.Id;

Basically, get the potential strings that match the chosen language and get all the default strings, then aggregate so you only pick one per Id - prioritize on the chosen language, then take the default as a fallback.

You can probably do similar things with UNION/EXCEPT but I suspect this will almost always lead to multiple scans against the same objects.

StackExchange Database Administrators Q#98575, answer score: 17