7FBBUes1

1MainID | StatusOrder | LanguageID | ClientID | Description   | Disclaimer  | Other
2-------+-------------+------------+----------+---------------+-------------+------
350     | 10          | 1          | 1        | Some text     | Disclaimer1 | Blah
450     | 10          | 2          | 1        | Otro texto    | NULL        | Blah
550     | 20          | 1          | 2        | Modified text | NULL        | Blah
655     | 10          | 1          | 1        | Some text 2   | Disclaimer2 | Blah Blah
755     | 10          | 1          | 2        | NULL          | Disclaimer3 | Blah Blah
8
9DROP TABLE IF EXISTS #main
10CREATE TABLE #main
11(
12    ID INT NOT NULL,
13    DescriptionID INT NOT NULL,
14    DisclaimerID INT NOT NULL,
15    Other NVARCHAR(500)
16)
17
18Drop table if exists #fact
19CREATE TABLE #fact
20(
21    FactID INT NOT NULL,
22    LanguageID INT NOT NULL,
23    StatusID INT NOT NULL,
24    ClientID INT NOT NULL,
25    Description NVARCHAR(MAX)
26)
27
28DROP TABLE IF EXISTS #status
29CREATE TABLE #status
30(
31    StatusID INT NOT NULL,
32    [Order] INT NOT NULL
33)
34
35INSERT INTO #main
36    (ID, DescriptionID, DisclaimerID, Other)
37    VALUES
38    (50, 1, 2, 'Blah'),
39    (55, 4, 3, 'Blah Blah')
40
41INSERT INTO #fact
42    (FactID, LanguageID, StatusID, ClientID, Description)
43    VALUES
44    (1, 1, 1, 1, N'Some text'),
45    (1, 2, 1, 1, N'Otro texto'),
46    (1, 1, 3, 2, N'Modified text'),
47    (2, 1, 1, 1, N'Disclaimer1'),
48    (3, 1, 1, 1, N'Disclaimer2'),
49    (3, 1, 2, 1, N'Disclaimer3'),
50    (4, 1, 1, 1, N'Some text 2')
51
52INSERT INTO #status
53    (StatusID, [Order])
54    VALUES
55    (1, 10),
56    (2, 100),
57    (3, 20)
58	
59SELECT
60    t.ID, t.Other,
61    fDescription.Description,
62    fDisclaimer.Description Disclaimer,
63    COALESCE(fDescription.LanguageID, fDisclaimer.LanguageID) LanguageID,
64    COALESCE(fDescription.ClientID, fDisclaimer.ClientID) ClientID,
65    COALESCE(fDescription.StatusID, fDisclaimer.StatusID) StatusID
66FROM #main t
67JOIN #fact fDescription
68    ON fDescription.FactID = t.DescriptionID
69LEFT OUTER JOIN #fact fDisclaimer
70    ON fDisclaimer.FactID = t.DisclaimerID
71    AND fDisclaimer.ClientID = fDescription.ClientID
72    AND fDisclaimer.LanguageID = fDescription.LanguageID
73    AND fDisclaimer.StatusID = fDescription.StatusID