Wybierz wszystkie rekordy, połącz z tabelą A, jeśli istnieje łączenie, tabela B, jeśli nie

Oto mój scenariusz:

Pracuję nad lokalizacją dla mojego projektu i zwykle robiłbym to w kodzie C #, jednak chcę to zrobić trochę więcej w SQL, ponieważ próbuję trochę wzmocnić mój SQL.

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

Uwaga: sam język programowania powinien być nieistotny, podaję go tylko dla kompletności.

W pewnym sensie osiągnąłem to, czego chciałem, ale nie w takim stopniu, w jakim chciałem. Minęło trochę czasu (przynajmniej rok), odkąd stworzyłem dowolne SQL JOINpoza podstawowymi i jest to dość skomplikowane JOIN.

Oto schemat odpowiednich tabel bazy danych. (Jest o wiele więcej, ale nie jest to konieczne w tej części.)

Wszystkie relacje opisane na obrazie są kompletne w bazie danych - PKi FKwszystkie ograniczenia są konfigurowane i działają. Żadna z opisanych kolumn nie jest w nullstanie. Wszystkie tabele mają schemat dbo.

Teraz mam zapytanie, które prawie robi to, co chcę: to znaczy, biorąc pod uwagę DOWOLNY identyfikator SupportCategoriesi DOWOLNY identyfikator Languages, zwróci albo:

Jeśli istnieje tłumaczenie prawym właściwy dla danego języka do tego łańcucha (czyli StringKeyId-> StringKeys.Idistnieje, i LanguageStringTranslations StringKeyId, LanguageIdi StringTranslationIdpołączenie istnieje, to ładunki StringTranslations.Textza to StringTranslationId.

Jeżeli LanguageStringTranslations StringKeyId, LanguageIdi StringTranslationIdpołączenie było nie istnieje, to wczytuje StringKeys.Namewartość. To Languages.Idjest dane integer.

Moje zapytanie, czy to bałagan, wygląda następująco:

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.LanguageStringTranslations.StringKeyId
    INNER JOIN dbo.StringTranslations
        ON dbo.StringTranslations.Id = dbo.LanguageStringTranslations.StringTranslationId
WHERE dbo.LanguageStringTranslations.LanguageId = 38 AND dbo.SupportCategories.Id = 0) END AS Result FROM (SELECT (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.LanguageStringTranslations.StringKeyId
    INNER JOIN dbo.StringTranslations
        ON dbo.StringTranslations.Id = dbo.LanguageStringTranslations.StringTranslationId
WHERE dbo.LanguageStringTranslations.LanguageId = 5 AND dbo.SupportCategories.Id = 0) AS x) AS T

Problemem jest to, że nie jest w stanie zapewnić mi ALL z SupportCategoriesich odpowiednimi StringTranslations.Textjeśli istnieje, lub ich StringKeys.Namejeśli nie istnieje. Doskonale nadaje się do zapewnienia któregokolwiek z nich, ale wcale nie. Zasadniczo, jest to, aby wymusić, że jeśli język nie posiada tłumaczenie dla konkretnego klucza, po czym domyślnym jest użycie StringKeys.Nameco jest StringKeys.DefaultLanguageIdtłumaczeniem. (Idealnie nie zrobiłby tego, ale zamiast tego wczytuje tłumaczenie, dla StringKeys.DefaultLanguageIdktórego mogę zrobić sam, jeśli wskażę właściwy kierunek dla reszty zapytania).

Spędziłem na tym DUŻO czasu i wiem, że gdybym tylko napisał to w C # (jak zwykle to robię), byłoby to już zrobione. Chcę to zrobić w języku SQL i mam problem z uzyskaniem danych wyjściowych, które lubię.

Jedynym zastrzeżeniem jest to, że chcę ograniczyć liczbę zastosowanych zapytań. Wszystkie kolumny są indeksowane i tak jak na razie je lubię, i bez prawdziwych testów warunków skrajnych nie mogę ich dalej indeksować.

Edycja: Kolejna uwaga, staram się utrzymywać bazę danych tak znormalizowaną, jak to możliwe, więc nie chcę powielać rzeczy, jeśli mogę tego uniknąć.

Przykładowe dane

Źródło

dbo.SupportCategories (całość):

Id  StringKeyId
0   0
1   1
2   2

dbo.Languages ​​(185 rekordów, pokazano tylko dwa dla przykładów):

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
1   48  8 -- added as example

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
8   Les APIs -- added as example

Wyjście prądowe

Biorąc pod uwagę dokładne zapytanie poniżej, generuje:

Result
Billing

Pożądane wyjście

Idealnie chciałbym móc pominąć konkretny SupportCategories.Idi uzyskać wszystkie z nich, tak jak jest (niezależnie od tego, czy Englishużyto języka 38 , czy 48 French, czy DOWOLNEGO innego języka w tej chwili):

Id  Result
0   Billing
1   API
2   Sales

Dodatkowy przykład

Biorąc pod uwagę, że mam dodać lokalizację dla French(tj. Dodać 1 48 8do LanguageStringTranslations), dane wyjściowe zmieniłyby się na (uwaga: to tylko przykład, oczywiście dodałbym zlokalizowany ciąg StringTranslations) do (zaktualizowany o przykład z Francji):

Result
Les APIs

Dodatkowe pożądane wyjście

Biorąc pod uwagę powyższy przykład, pożądane byłyby następujące dane wyjściowe (zaktualizowane o przykład francuski):

Id  Result
0   Billing
1   Les APIs
2   Sales

(Tak, wiem technicznie, że jest to niewłaściwe z punktu widzenia spójności, ale tego właśnie można by się spodziewać w tej sytuacji).

Edytować:

Małe zaktualizowane, zmieniłem strukturę dbo.Languagestabeli, upuściłem Id (int)z niej kolumnę i zastąpiłem ją Abbreviation(która jest teraz przemianowana na Idi wszystkie względne klucze obce i relacje zaktualizowane). Z technicznego punktu widzenia jest to, moim zdaniem, bardziej odpowiednia konfiguracja, ponieważ tabela jest ograniczona do kodów ISO 639-1, które są unikalne na początek.

Tl; dr

Tak: pytanie, jak mogę zmodyfikować tę kwerendę, aby powrócić wszystko od SupportCategoriesa następnie powrócić albo StringTranslations.Textza to StringKeys.Id, Languages.Idkombinacji, lubStringKeys.Name Jeśli tak nie istnieje?

Moją początkową myślą jest to, że mogę w jakiś sposób przerzucić bieżące zapytanie na inny typ tymczasowy jako inne podkwerendę i zawinąć to zapytanie w jeszcze jedną SELECTinstrukcję i wybrać dwa pola, które chcę ( SupportCategories.Idi Result).

Jeśli nic nie znajdę, zrobię standardową metodę, której zwykle używam, czyli załadowanie wszystkich SupportCategoriesdo mojego projektu w języku C #, a następnie uruchomię ręcznie dla każdego z powyższych zapytań SupportCategories.Id.

Dzięki za wszelkie sugestie / komentarze / krytykę.

Przepraszam też za to, że był absurdalnie długi, po prostu nie chcę żadnych dwuznaczności. Często używam StackOverflow i widzę pytania, które nie mają treści, nie chciałem tutaj popełnić tego błędu.

Oto pierwsze podejście, jakie wymyśliłem:

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;

Zasadniczo, pobierz potencjalne ciągi, które pasują do wybranego języka i uzyskaj wszystkie ciągi domyślne, a następnie agreguj, aby wybrać tylko jeden Idpriorytet dla wybranego języka, a następnie przyjąć domyślną jako rezerwową.

Prawdopodobnie możesz robić podobne rzeczy za pomocą UNION/, EXCEPTale podejrzewam, że prawie zawsze doprowadzi to do wielokrotnego skanowania tych samych obiektów.

Alternatywne rozwiązanie, które pozwala uniknąć INgrupowania w odpowiedzi Aarona:

DECLARE 
    @SelectedLanguageId integer = 48;

SELECT 
    SC.Id,
    SC.StringKeyId,
    Result =
        CASE
            -- No localization available
            WHEN LST.StringTranslationId IS NULL
            THEN SK.Name
            ELSE
            (
                -- Localized string
                SELECT ST.[Text]
                FROM dbo.StringTranslations AS ST
                WHERE ST.Id = LST.StringTranslationId
            )
        END
FROM dbo.SupportCategories AS SC
JOIN dbo.StringKeys AS SK
    ON SK.Id = SC.StringKeyId
LEFT JOIN dbo.LanguageStringTranslations AS LST
    WITH (FORCESEEK) -- Only for low row count in sample data
    ON LST.StringKeyId = SK.Id
    AND LST.LanguageId = @SelectedLanguageId;

Jak wspomniano, FORCESEEKpodpowiedź jest wymagana tylko w celu uzyskania najbardziej wydajnego planu ze względu na małą liczność LanguageStringTranslationstabeli z dostarczonymi przykładowymi danymi. Przy większej liczbie wierszy optymalizator wybierze wyszukiwanie indeksu w sposób naturalny.

Sam plan wykonania ma ciekawą funkcję:

Właściwość Przekaż przy ostatnim złączeniu zewnętrznym oznacza, że ​​wyszukiwanie w StringTranslationstabeli jest wykonywane tylko wtedy, gdy wcześniej w niej znaleziono wiersz LanguageStringTranslations. W przeciwnym razie wewnętrzna strona tego połączenia zostanie całkowicie pominięta dla bieżącego wiersza.

Tabela DDL

CREATE TABLE dbo.Languages
(
    Id integer NOT NULL,
    Abbreviation char(2) NOT NULL,
    Family nvarchar(96) NOT NULL,
    Name nvarchar(96) NOT NULL,
    [Native] nvarchar(96) NOT NULL,

    CONSTRAINT PK_dbo_Languages
        PRIMARY KEY CLUSTERED (Id)
);

CREATE TABLE dbo.StringTranslations
(
    Id bigint NOT NULL,
    [Text] nvarchar(128) NOT NULL,

    CONSTRAINT PK_dbo_StringTranslations
    PRIMARY KEY CLUSTERED (Id)
);

CREATE TABLE dbo.StringKeys
(
    Id bigint NOT NULL,
    Name varchar(64) NOT NULL,
    DefaultLanguageId integer NOT NULL,

    CONSTRAINT PK_dbo_StringKeys
    PRIMARY KEY CLUSTERED (Id),

    CONSTRAINT FK_dbo_StringKeys_DefaultLanguageId
    FOREIGN KEY (DefaultLanguageId)
    REFERENCES dbo.Languages (Id)
);

CREATE TABLE dbo.SupportCategories
(
    Id integer NOT NULL,
    StringKeyId bigint NOT NULL,

    CONSTRAINT PK_dbo_SupportCategories
        PRIMARY KEY CLUSTERED (Id),

    CONSTRAINT FK_dbo_SupportCategories
    FOREIGN KEY (StringKeyId)
    REFERENCES dbo.StringKeys (Id)
);

CREATE TABLE dbo.LanguageStringTranslations
(
    StringKeyId bigint NOT NULL,
    LanguageId integer NOT NULL,
    StringTranslationId bigint NOT NULL,

    CONSTRAINT PK_dbo_LanguageStringTranslations
    PRIMARY KEY CLUSTERED 
        (StringKeyId, LanguageId, StringTranslationId),

    CONSTRAINT FK_dbo_LanguageStringTranslations_StringKeyId
    FOREIGN KEY (StringKeyId)
    REFERENCES dbo.StringKeys (Id),

    CONSTRAINT FK_dbo_LanguageStringTranslations_LanguageId
    FOREIGN KEY (LanguageId)
    REFERENCES dbo.Languages (Id),

    CONSTRAINT FK_dbo_LanguageStringTranslations_StringTranslationId
    FOREIGN KEY (StringTranslationId)
    REFERENCES dbo.StringTranslations (Id)
);

Przykładowe dane

INSERT dbo.Languages
    (Id, Abbreviation, Family, Name, [Native])
VALUES
    (38, 'en', N'Indo-European', N'English', N'English'),
    (48, 'fr', N'Indo-European', N'French', N'français, langue française');

INSERT dbo.StringTranslations
    (Id, [Text])
VALUES
    (0, N'Billing'),
    (1, N'API'),
    (2, N'Sales'),
    (3, N'Open'),
    (4, N'Waiting for Customer'),
    (5, N'Waiting for Support'),
    (6, N'Work in Progress'),
    (7, N'Completed'),
    (8, N'Les APIs'); -- added as example

INSERT dbo.StringKeys
    (Id, Name, DefaultLanguageId)
VALUES
    (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);

INSERT dbo.SupportCategories
    (Id, StringKeyId)
VALUES
    (0, 0),
    (1, 1),
    (2, 2);

INSERT dbo.LanguageStringTranslations
    (StringKeyId, LanguageId, StringTranslationId)
VALUES
    (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