Using Boolean Fields in a View to Identify Linked Tables



  • 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
    );

    -- 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?



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

    https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=d5fe0c98711a9071e38e24fd73b1481b




Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2