CODEWITHSUNDEEP
sqlsql-server-2012common-table-expression
Sandeep Thomas
Sandeep Thomas

Reputation: 4727

Get top level parents ID inherit to all children in SQL

I've a table like this

ID    Name     Parent    Code
----------------------------------
1     Item1     NULL     K123
2     Item2     NULL     K324
3     Item3      1       NULL
4     Item4      2       NULL
5     Item5      3       NULL
6     Item6      5       NULL
7     Item7      4       NULL
8     Item8      NULL    K567
9     Item9      8       NULL
10    Item10     NULL    NULL
---------------------------------

I need to inherit the code to all children from its parent like this

ID    Name     Parent    Code
----------------------------------
1     Item1     NULL     K123
2     Item2     NULL     K324
3     Item3      1       K123
4     Item4      2       K324
5     Item5      3       K123
6     Item6      5       K123
7     Item7      4       K324
8     Item8      NULL    K567
9     Item9      8       K567
10    Item10     NULL    NULL
---------------------------------

I tried the CTE below

;with CTE(ID,Name,Parent,Code,[Level]) as
(
    select ID,Name,Parent,Code,0 as [Level] from tbl_mytable 
    union all
    select T.ID,T.Name,T.Parent,T.Code,C.[Level]+1 from tbl_mytable T
    inner join CTE C on C.Parent=T.ID
)
select * from CTE C

Which doesnt help me to achieve what I am trying. I'm just a beginner with CTE with which I dont think it can fix.

Upvotes: 1

Views: 47

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 520968

You were on the right track to use a recursive CTE, but your logic is a bit off. Consider this working version:

WITH cte AS (
    SELECT * FROM tbl_mytable WHERE Parent IS NULL
    UNION ALL
    SELECT t1.ID, t1.Name, t1.Parent, COALESCE(t1.Code, t2.Code)
    FROM tbl_mytable t1
    INNER JOIN cte t2
        ON t1.Parent = t2.ID
)

SELECT *
FROM cte
ORDER BY ID;

enter image description here

Demo

The base case component of the recursive CTE (i.e. what comes before the union) should be those records having no parents. This is how we know they are the highest parent level. Then, we join the CTE recursively by matching a given level to the parent immediately above it. Then, we use a trick to bring down the codes from the top level:

COALESCE(t1.Code, t2.Code)

This will start with one of the parent's codes, and then will propagate downwards to the various levels of children.

Upvotes: 5

Related Questions