0

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?

3 Answers 3

3

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

8
  • 1
    Added an updated example with a case expression that maps true/false to 1/0 May 17, 2022 at 8:19
  • 1
    Maybe cast to bit also May 17, 2022 at 9:14
  • 1
    The CASE and the BIT cast were exactly what was needed. This is a tremendous help. Sure appreciate it!
    – Dave
    May 17, 2022 at 10:39
  • 1
    The one advantage I could think of was that the domain is restricted to 0/1, but one can achieve the same thing with a check constraint. Anyhow, thanks for your answer, I don't normally use SQL-server so I was curious whether bit is some kind of best practice there. May 17, 2022 at 11:13
  • 2
    FWIW, regarding the bit type in MSSQL: 8 bit columns in a table fits on one byte, so there is a possible storage saving aspect compared to, say, tinyint. Bit is weird in the sense that at parsing stage 0 and 'false' parses to 0. <> 0 and 'true' parses to 1 (for instance 42 parses to 1). Parsing the strings 'false' to 0 and 'true' to 1 doesn't make it Boolean - IMO it only confuses us. May 17, 2022 at 16:03
1

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
4
  • What do you mean with unknown (if not null) in case of boolean? May 17, 2022 at 15:17
  • @Lennart : UNKNOWN would be evaluation of an expression, for instance. I.e., the third truth state (in addition to TRUE and FALSE). Also, all types should allow for NULL. I.e., the distinction between UNKNOWN and NULL is "I don't know" vs "I know that the data is missing". ANSI SQL didn't bother to sort out the difference between the two. May 17, 2022 at 15:52
  • Ok, I see what you mean. I thought bit had some magical extension to cope with 4VL (MySQL had (has?) a peculiar timestamp 0000-00-... where ts IS NULL = ts IS NOT NULL = true ). Personally, I don't think 4VL will solve any issues that we are experiencing with 3VL. One can easily imagine other situations that would require 5VL and so forth. I try to avoid 3VL whenever practically possible and when not, make sure the meaning of null is well defined for that situation. May 18, 2022 at 5:57
  • Bit isn't even an attempt to be Boolean in the first place, so no 4VL stuff in there. Note that I'm not referring to Codd's 4VL above - that would probably lead to 5 (or 6?) truth states for a "Boolean" in such a universe. Adding Codd's 4VL to the already messy distinction between "It is known to be unknown" vs "It isn't known whether it is known or unknown" makes my head hurts. (And, yes, why stop with 4VL? I read about a study that came up with 17 (or was it 27) different meanings of "null".) I'm all with you regarding your pragmatical way to deal with 3 VL. May 18, 2022 at 9:19
0

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];

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.