Sql-server – JOIN 3 Tables without Union ALL nor nested queries with multiple IDs

join;sql serverunionview

Not sure if this is possible, but I would like to join multiple tables with multiple IDs without UNION ALL or nested queries. You can see the related question here which shows everything in table view. I would like to create a table view but am not allowed to use what was mentioned above. I've already figured out how to do it with UNION ALL.

So, I have the Main table which reference a table called Fact. The Fact table contains data that can be referenced by LanguageID, ClientID, or StatusID (revision). The Main and Fact tables are related by an intermediary table called FactLink (not shown for simplicity's sake).

The end result MainView indexable table I would like to get looks like:

MainView

MainID | StatusOrder | LanguageID | ClientID | Description   | Disclaimer  | Other
-------+-------------+------------+----------+---------------+-------------+------
50     | 10          | 1          | 1        | Some text     | Disclaimer1 | Blah
50     | 10          | 2          | 1        | Otro texto    | NULL        | Blah
50     | 20          | 1          | 2        | Modified text | NULL        | Blah
55     | 10          | 1          | 1        | Some text 2   | Disclaimer2 | Blah Blah
55     | 10          | 1          | 2        | NULL          | Disclaimer3 | Blah Blah

DROP TABLE IF EXISTS #main
CREATE TABLE #main
(
    ID INT NOT NULL,
    DescriptionID INT NOT NULL,
    DisclaimerID INT NOT NULL,
    Other NVARCHAR(500)
)

Drop table if exists #fact
CREATE TABLE #fact
(
    FactID INT NOT NULL,
    LanguageID INT NOT NULL,
    StatusID INT NOT NULL,
    ClientID INT NOT NULL,
    Description NVARCHAR(MAX)
)

DROP TABLE IF EXISTS #status
CREATE TABLE #status
(
    StatusID INT NOT NULL,
    [Order] INT NOT NULL
)

INSERT INTO #main
    (ID, DescriptionID, DisclaimerID, Other)
    VALUES
    (50, 1, 2, 'Blah'),
    (55, 4, 3, 'Blah Blah')

INSERT INTO #fact
    (FactID, LanguageID, StatusID, ClientID, Description)
    VALUES
    (1, 1, 1, 1, N'Some text'),
    (1, 2, 1, 1, N'Otro texto'),
    (1, 1, 3, 2, N'Modified text'),
    (2, 1, 1, 1, N'Disclaimer1'),
    (3, 1, 1, 1, N'Disclaimer2'),
    (3, 1, 2, 1, N'Disclaimer3'),
    (4, 1, 1, 1, N'Some text 2')

INSERT INTO #status
    (StatusID, [Order])
    VALUES
    (1, 10),
    (2, 100),
    (3, 20)

Here's what I have so far. But it doesn't include the last entry and hasn't accounted for the StatusOrder. I'm not sure where to go from here.

SELECT
    t.ID, t.Other,
    fDescription.Description,
    fDisclaimer.Description Disclaimer,
    COALESCE(fDescription.LanguageID, fDisclaimer.LanguageID) LanguageID,
    COALESCE(fDescription.ClientID, fDisclaimer.ClientID) ClientID,
    COALESCE(fDescription.StatusID, fDisclaimer.StatusID) StatusID
FROM #main t
JOIN #fact fDescription
    ON fDescription.FactID = t.DescriptionID
LEFT OUTER JOIN #fact fDisclaimer
    ON fDisclaimer.FactID = t.DisclaimerID
    AND fDisclaimer.ClientID = fDescription.ClientID
    AND fDisclaimer.LanguageID = fDescription.LanguageID
    AND fDisclaimer.StatusID = fDescription.StatusID

Best Answer

I will answer the question as asked but I expect that it won't solve your actual problem. You want to translate the query that you have in the other post into a query that can be turned into an indexed view. The biggest problem that I see is that your data model requires that data from multiple rows of the "fact" table be combined together into single rows. Such a requirement fights against the indexed view functionality. Many of the T-SQL restrictions almost seem designed around stopping that type of operation (along with others):

index view restrictions

The most important entries in that list for your problem are the prohibitions on MAX, MIN, self-joins, subqueries, APPLY, and UNPIVOT. All of those can be used to rewrite your UNION ALL query but none of them are allowed in indexed views. There is a way to rewrite it but you'll just get stuck further down the line. You started with the following query:

SELECT
  Main.ID,
  s.[Order],
  f.LanguageID,
  f.ClientID,
  f.Description,
  NULL Disclaimer,
  Main.Other
FROM Main
JOIN Fact f
ON f.FactID = Main.DescriptionID
JOIN Status s ON s.StatusID = f.StatusID
UNION ALL
SELECT
  Main.ID,
  s.[Order],
  f.LanguageID,
  f.ClientID,
  NULL Description,
  f.Description Disclaimer,
Main.Other
FROM Main
JOIN Fact f
ON f.FactID = Main.DisclaimerID
JOIN Status s ON s.StatusID = f.StatusID;

There is at least one way to rewrite that query in a way that's a valid indexed view definition. First create a dummy table with just two rows:

CREATE TABLE DIM_NUMBERS (NUM INT NOT NULL);
INSERT INTO DIM_NUMBERS VALUES (1), (2);

You can then define your view like this:

CREATE VIEW dbo.X_VIEW
WITH SCHEMABINDING  
AS  
SELECT
  Main.ID,
  s.[Order],
  f.LanguageID,
  f.ClientID,
  CAST(CASE WHEN d.NUM = 1 THEN f.Description ELSE NULL END AS NVARCHAR(2000)) [Description],
  CAST(CASE WHEN d.NUM = 2 THEN f.Description ELSE NULL END AS NVARCHAR(2000)) Disclaimer,
  Main.Other
FROM dbo.Main
CROSS JOIN dbo.DIM_NUMBERS d
JOIN dbo.Fact f
  ON (f.FactID = Main.DescriptionID AND d.NUM = 1) OR (f.FactID = Main.DisclaimerID AND d.NUM = 2)
JOIN dbo.Status s ON s.StatusID = f.StatusID;

CREATE UNIQUE CLUSTERED INDEX IDX_X_VIEW   
    ON dbo.X_VIEW  (ID, [Order], LanguageID, ClientID, [Description], Disclaimer, Other);

I have rewritten your query not to use UNION ALL but I did not include the GROUP BY because MIN and MAX are not allowed in indexed views. There aren't any aggregate functions that will help you here because you have an NVARCHAR column. If you had a numeric column then you could probably pull this off with a SUM(COALESCE(COLUMN_NAME, 0)) column but you can't apply the SUM aggregate to NVARCHAR.

Instead of trying to shoehorn a query into an indexed view definition I would try to think of other solutions. Could you just store the results in a table instead? Depending on the length of the description column the space difference between that and an indexed view might not be that large. Can you change the data model in some way that helps?

Related Question