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;