Using Boolean Fields in a View to Identify Linked Tables

by

The question:

I am using SQL Server 2019. I am using SQL Server Management Studio on the client side.

Suppose I have the following data model:

-- A record of the overall test of a  widget
CREATE TABLE [TestRecords]
(
   [TestRecord_ID] BIGINT NOT NULL,
   -- ...

   CONSTRAINT PK_TestRecords PRIMARY KEY ([TestRecord_ID])
);

-- A record of an individual subtest within the overall test
-- Any given subtest may be repeated N times
CREATE TABLE [SubtestRuns]
(
   [TestRecord_ID] BIGINT NOT NULL,
   [TestType] VARCHAR(50) NOT NULL,

   -- [RunNumber] is unique within one overall test, but not across all time
   [RunNumber] INT NOT NULL,

   -- Fields common to all subtests
   -- ...

   CONSTRAINT PK_SubtestRuns PRIMARY KEY ([TestRecord_ID], [TestType], [RunNumber]),

   CONSTRAINT FK_SubtestRuns_TestRecord_ID
              FOREIGN KEY ([TestRecord_ID])
              REFERENCES [TestRecords]([TestRecord_ID])
);

-- This is the first of many subtest types
CREATE TABLE [Subtest_1]
(
   [TestRecord_ID] BIGINT NOT NULL,
   [RunNumber] INT NOT NULL,
   [TestType] VARCHAR(50) NOT NULL CONSTRAINT DF_Subtest_1_TestType DEFAULT 'Subtest_1',

   -- Fields specific to this subtest type
   -- ...

   CONSTRAINT PK_Subtest_1 PRIMARY KEY ([TestRecord_ID], [RunNumber]),
   CONSTRAINT CHK_Subtest_1_TestType_EQ_Subtest_1 CHECK ([TestType] = 'Subtest_1'),

   CONSTRAINT FK_Subtest_1_TestRecord_ID_RunNumber_TestType
              FOREIGN KEY ([TestRecord_ID], [TestType], [RunNumber])
              REFERENCES [SubtestRuns]([TestRecord_ID], [TestType], [RunNumber])
);

-- This is the second of many subtest types
CREATE TABLE [Subtest_2]
(
   [TestRecord_ID] BIGINT NOT NULL,
   [RunNumber] INT NOT NULL,
   [TestType] VARCHAR(50) NOT NULL CONSTRAINT DF_Subtest_2_TestType DEFAULT 'Subtest_2',

   -- Fields specific to this subtest type
   -- ...

   CONSTRAINT PK_Subtest_2 PRIMARY KEY ([TestRecord_ID], [RunNumber]),
   CONSTRAINT CHK_Subtest_2_TestType_EQ_Subtest_2 CHECK ([TestType] = 'Subtest_2'),

   CONSTRAINT FK_Subtest_2_TestRecord_ID_RunNumber_TestType
              FOREIGN KEY ([TestRecord_ID], [TestType], [RunNumber])
              REFERENCES [SubtestRuns]([TestRecord_ID], [TestType], [RunNumber])
);

-- And so on for other subtest types...

I would now like to create a view that is the same as [TestRecords] but which adds Boolean columns to indicate if data for each subtest type is present.

CREATE VIEW [TestRecords_View] AS
SELECT DISTINCT
   [TestRecords].[TestRecord_ID],
   -- Remaining [TestRecords] fields
   -- ...

   [Has_Subtest_1_Data] BIT NOT NULL,
   [Has_Subtest_2_Data] BIT NOT NULL,
   -- ...

   FROM [TestRecords]
   -- ...

I am having trouble figuring out how to assign values to the computed [Has_Subtest_N_data] fields. How may I populate these fields?

Edit to Add Attempt via Computed Field on Left Outer Join

CREATE VIEW [TestRecords_View] AS
SELECT DISTINCT
   [T0].[TestRecord_ID],
   -- Remaining [TestRecords] fields
   -- ...

   -- These lines yield this error:
   -- Incorrect syntax near '.'.
   [Has_Subtest_1_Data] AS [T1].[TestRecord_ID] IS NOT NULL,
   [Has_Subtest_2_Data] AS [T2].[TestRecord_ID] IS NOT NULL,
   -- ...

   FROM [TestRecords] AS [T0]
   LEFT OUTER JOIN [Subtest_1] AS [T1]
   ON [T0].[TestRecord_ID] = [T1].[TestRecord_ID]
   LEFT OUTER JOIN [Subtest_2] AS [T2]
   ON [T0].[TestRecord_ID] = [T2].[TestRecord_ID]

   -- ...

As indicated in the inline comments, this yields a syntax error.

Is it syntactically possible in T-SQL to do what I am attempting?

The Solutions:

Below are the methods you can try. The first solution is probably the best. Try others if the first one doesn’t work. Senior developers aren’t just copying/pasting – they read the methods carefully & apply them wisely to each case.

Method 1

Something like:

CREATE VIEW v AS (
    SELECT t.TestRecord_ID
         , ...
         , s1.TestRecord_ID IS NOT NULL AS Has_Subtest_1_Data
         , s2.TestRecord_ID IS NOT NULL AS Has_Subtest_2_Data
         , ...
    FROM TestRecords t
    LEFT JOIN Subtest_1 s1
        ON ...
    LEFT JOIN Subtest_2 s2
        ON ... 
    ...

EDIT: SQL server does not seem to allow for boolean expressions. Added CASE expression

CREATE VIEW [TestRecords_View] AS
SELECT DISTINCT
   [T0].[TestRecord_ID],

   CASE WHEN [T1].[TestRecord_ID] IS NOT NULL THEN 1 ELSE 0 END AS Has_Subtest_1_Data,
   CASE WHEN [T2].[TestRecord_ID] IS NOT NULL THEN 1 ELSE 0 END AS Has_Subtest_2_Data

   FROM [TestRecords] AS [T0]
   LEFT OUTER JOIN [Subtest_1] AS [T1]
       ON [T0].[TestRecord_ID] = [T1].[TestRecord_ID]
   LEFT OUTER JOIN [Subtest_2] AS [T2]
       ON [T0].[TestRecord_ID] = [T2].[TestRecord_ID];

Fiddle

Method 2

For the record, there is no Boolean data type in SQL Server. Bit is a numerical type, limited to the values 0 and 1 (and NULL).

That is not the same as a Boolean type, which could be true, false, unknown (and NULL).

If there was a Boolean type, you could do something like:

SELECT TestRecord_ID
FROM c
WHERE Has_Subtest_1_Data

But in SQL Server you have to decide which you consider true, probably 1, and then do:

SELECT TestRecord_ID
FROM c
WHERE Has_Subtest_1_Data = 1

Method 3

A colleague pointed me to an alternative to joins that performs much better than joins. I will post it here for future reference.

I will leave the original answer (by Lennart) marked since it is a correct answer and since it addresses the problem via the initial angle I was taking (joins).

CREATE VIEW [TestRecords_View] AS
SELECT DISTINCT
   [T0].*,
   CASE WHEN EXISTS (SELECT * FROM [Subtest_1] AS [TD] WHERE [TD].[Test_Record_ID] = [T0].[Test_Record_ID]) THEN CAST(1 AS BIT) ELSE CAST(0 AS BIT) END AS [Has_Subtest_1_Data],
   CASE WHEN EXISTS (SELECT * FROM [Subtest_2] AS [TD] WHERE [TD].[Test_Record_ID] = [T0].[Test_Record_ID]) THEN CAST(1 AS BIT) ELSE CAST(0 AS BIT) END AS [Has_Subtest_2_Data],
   -- ...
FROM [TestRecords] AS [T0];


All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Comment