Skip to content
Advertisement

MS SQL Server view with conditional values

I have a table A that can reference to itself to inherit some values from a parent. The reference is represented with ParentID. Parents have null values in ParentID.

ID ParentID Field A Field B Field C
1 NULL ValueA1 ValueB1 ValueC1
2 NULL ValueA2 ValueB2 ValueC2
3 1 null ValueB3 null
4 1 null ValueB4 null
5 2 ValueA5 ValueB5 null
6 NULL ValueA6 ValueB6 ValueC6

and another table A_Inheritance to determine if a value from any field should be inherited from the parent:

ID A_ID Inherits A Inherits B Inherits C
1 3 True False true
2 4 True False true
3 5 False False true

The first two rows in Table A are parents. The other 3 rows are children that inherit some values. The values to be inherited are specified in the table A_Inheritance.

Therefore the view should contain the following values:

ID ParentID Field A Field B Field C
1 NULL ValueA1 ValueB1 ValueC1
2 NULL ValueA2 ValueB2 ValueC2
3 1 ValueA1 ValueB3 ValueC1
4 1 ValueA1 ValueB4 ValueC1
5 2 ValueA5 ValueB5 ValueC2
6 NULL ValueA6 ValueB6 ValueC6

Is it possible to create a view like it in SQL Server? I am trying to write some selects but I am struggling when I write to use the IF statement.

Any help will be appreciated.

Kind regards

Advertisement

Answer

If you only have one level of inheritance, then this is a simple LEFT JOIN query, as mentioned in the comments by @lptr.

Assuming you actually have nested (recursive) levels of inheritance, you would need a recursive CTE

WITH cte AS (
    SELECT
      A.Id,
      A.ParentId,
      TopParent = A.ParentId,
      A.fieldA,
      A.fieldB,
      A.fieldC,
      ai.InheritsA,
      ai.InheritsB,
      ai.InheritsC
    FROM A  -- get bottom children
    LEFT JOIN A_inheritance ai ON ai.A_Id = A.Id  -- and their inheritance rules
    
    UNION ALL
    
    SELECT
      cte.Id,
      cte.ParentId,
      A.ParentId,
      CASE WHEN cte.InheritsA = 'True' THEN ISNULL(cte.fieldA, A.fieldA) ELSE cte.fieldA END,
      CASE WHEN cte.InheritsB = 'True' THEN ISNULL(cte.fieldB, A.fieldB) ELSE cte.fieldB END,
      CASE WHEN cte.InheritsC = 'True' THEN ISNULL(cte.fieldC, A.fieldC) ELSE cte.fieldC END,
      cte.InheritsA,
      cte.InheritsB,
      cte.InheritsC
    FROM A
    JOIN cte ON cte.TopParent = A.Id  -- join on all parents
)
SELECT
  A.Id,
  A.ParentId,
  A.fieldA,
  A.fieldB,
  A.fieldC
FROM cte A
WHERE A.TopParent IS NULL  -- only rows where we have reached the top
ORDER BY
  A.Id;

db<>fiddle

User contributions licensed under: CC BY-SA
8 People found this is helpful
Advertisement