MS SQL Server view with conditional values-CodePudding

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

CodePudding user response：

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