Return to Question

Edit:

Small updated, I did change the structure of the dbo.Languages table, and drop the Id (int) column from it, and replace it with Abbreviation (which is now renamed to Id, and all relative Foreign-Keys and and relationships updated). From a technical standpoint, this is a more appropriate setup in my opinion due to the fact that the table is limited to ISO 639-1 codes, which are unique to begin with.

StringKeyId LanguageId StringTranslationId 0 38 0 1 38 1 2 38 2 3 38 3 4 38 4 5 38 5 6 38 6 7 38 7 

Id Text 0 Billing 1 API 2 Sales 3 Open 4 Waiting for Customer 5 Waiting for Support 6 Work in Progress 7 Completed 

Given I were to add a localization for French (I.e. add 0 48 1 to LanguageStringTranslations), the output would change to (note: this is example only, obviously I would add a localized string to StringTranslations):

Result API 

Given the example above, the following output would be desired:

Id Result 0 API 1 API 2 Sales 

StringKeyId LanguageId StringTranslationId 0 38 0 1 38 1 2 38 2 3 38 3 4 38 4 5 38 5 6 38 6 7 38 7 1 48 8 -- added as example 

Id Text 0 Billing 1 API 2 Sales 3 Open 4 Waiting for Customer 5 Waiting for Support 6 Work in Progress 7 Completed 8 Les APIs -- added as example 

Given I were to add a localization for French (I.e. add 1 48 8 to LanguageStringTranslations), the output would change to (note: this is example only, obviously I would add a localized string to StringTranslations) (updated with French example):

Result Les APIs 

Given the example above, the following output would be desired (updated with French example):

Id Result 0 Billing 1 Les APIs 2 Sales 

Tl;dr

Thanks for any and all suggestions/comments/critique.

Example Data

Source

dbo.SupportCategories (Entirety):

Id StringKeyId 0 0 1 1 2 2 

dbo.Languages (185 records, only showing two for examples):

Id Abbreviation Family Name Native 38 en Indo-European English English 48 fr Indo-European French français, langue française 

dbo.LanguagesStringTranslations (Entirety):

StringKeyId LanguageId StringTranslationId 0 38 0 1 38 1 2 38 2 3 38 3 4 38 4 5 38 5 6 38 6 7 38 7 

dbo.StringKeys (Entirety):

Id Name DefaultLanguageId 0 Billing 38 1 API 38 2 Sales 38 3 Open 38 4 Waiting for Customer 38 5 Waiting for Support 38 6 Work in Progress 38 7 Completed 38 

dbo.StringTranslations (Entirety):

Id Text 0 Billing 1 API 2 Sales 3 Open 4 Waiting for Customer 5 Waiting for Support 6 Work in Progress 7 Completed 

Current Output

Given the exact query below, it outputs:

Result Billing 

Desired Output

Ideally, I would like to be able to omit the specific SupportCategories.Id, and get all of them, as so (regardless if language 38 English was used, or 48 French, or ANY other language at the moment):

Id Result 0 Billing 1 API 2 Sales 

Additional Example

Given I were to add a localization for French (I.e. add 0 48 1 to LanguageStringTranslations), the output would change to (note: this is example only, obviously I would add a localized string to StringTranslations):

Result API 

Additional Desired Output

Given the example above, the following output would be desired:

Id Result 0 API 1 API 2 Sales 

(Yes, I know technically that's wrong from a consistency standpoint, but it's what would be desired in the situation.)

Tl;dr

Thanks for any and all suggestions/comments/critique.

Also, I apologize for it being absurdly long, I just don't want any ambiguity. I'm often on StackOverflow and see questions that lack substance, didn't wish to make that mistake here.