T-SQL hierarchy query

Question

I have a table with hierarchical data:

This is a sample of data with id, parent id, name, code (which is sometimes not filled), level and isroot column. In real scenario there will be more levels than just 2 but now let's look at the simplified example.

What I need to do is to loop over all records and find rows where id is not filled at any level of the hierarchy:

• Rows with id ranging should be returned from 6 till 10 because they do not have code filled in at any point of the hierarchy
• Rows from 1 till 5 should not be returned because code was mentioned somewhere in the hierarchy.

How should one solve this problem with T-SQL?

The only solution that came to my mind was recursivity (cte or WHILE) but I what I was trying to implement was too complex and did not solve the problem.

Answer 1

Slightly different than @DhruvJoshi's answer, since it may be useful:

WITH recCTE AS
(
    SELECT 
        id, 
        parent_id, 
        CASE WHEN CODE IS NOT NULL THEN 1 ELSE 0 END as code_check,
        1 as depth,
        CAST(id as VARCHAR(50)) as path
    FROM table
    WHERE isRootLevel = 1
    UNION ALL
    SELECT
        table.id,
        table.parent_id,
        CASE WHEN CODE IS NOT NULL OR reccte.code_check = 1 THEN 1 ELSE 0 END,
        depth + 1 as depth,
        reccte.path + CAST(table.id AS varchar(10)) as path
    FROM
        recCTE 
        INNER JOIN table ON
            recCTE.ID = table.parent_id
    WHERE depth < 20 /*just in case you start cycling/endless looping*/
)
SELECT * FROM recCTE where code_check = 0 ORDER BY path, depth;

Answer 2

Query like this should work:

; with cte as 
(
select id, parent_id,code,parent_id as RootId from tableT where IsRootLevel=1
UNION ALL
select T2.id,T2.parent_id,T2.code,T1.RootId as RootId from tableT T2 join
cte T1 on T1.id=T2.parent_id and IsRootLevel=0
)   
,
cte2 as
(select id,MAX(case when code ='' then NULL else code end) over( partition by RootId) as code from cte)

select T1.* from tableT T1 left join cte2 T2 
on T1.id=T2.id
where T2.code is NULL

See working demo

Answer 3

Here is another example, for anyone who may still struggling with hierarchical data (like me). Say we got a following hierarchical structure:

CEO
|-- Sales Director  
?   |-- Sales Manager 1
?   `-- Sales Manager 2 
`-- Technical Director
    |-- Product Manager
    |-- R&D Team Lead
    `-- QA Team Lead

Using recursive cte to get the level for each node:

with cte as (
    select id, parentId, roleName, 1 as lvl from roles where id = 1 -- root node
    union all
    select r.id, r.parentId, r.roleName, cte.lvl+1 as lvl from roles r -- child nodes
    inner join cte on cte.id = r.parentid
)
select * from cte;

To get the path for each node:

with cte as (
    select id, roleName,     cast(roleName as varchar(200)) as hierPath 
    from roles where id = 1 
    union all
    select r.id, r.rolename, cast(cte.hierPath + ' / ' + r.rolename as varchar(200)) as hierPath
    from roles r 
    inner join cte on cte.id = r.parentid
)
select * from cte;

Using row_number() and power() to get the sorted hierarchy tree result (parent followed by all of its children, then all the children of each one of those and so on):

with cte as (
    select id, roleName, cast(roleName as varchar(200)) as hierPath, 1 as lvl, 
    row_number()over(partition by parentid order by roleName) / power(10.0,1) as sortNo
    from roles where id = 1 
    union all
    select r.id, r.rolename, cast(cte.hierPath + ' / ' + r.rolename as varchar(200)) as hierPath, cte.lvl+1 as lvl,
    cte.sortNo + row_number()over(partition by r.parentid order by r.roleName) / power(10.0,cte.lvl+1) as sortNo
    from roles r 
    inner join cte on cte.id = r.parentid
)
select * from cte 
order by sortNo;

---

Setup test data:

create table roles (
    id int not null,
    parentId int,
    roleName varchar(50) not null
);

insert into roles 
    (id, parentId, roleName)
values 
    (1, null, 'CEO'),
    (2, 1, 'Sales Director'),
    (3, 1, 'Technical Director'),
    (4, 2, 'Sales Manager 1'),
    (5, 2, 'Sales Manager 2'),
    (6, 3, 'Product Manager'),
    (7, 3, 'R&D Team Lead'),
    (8, 3, 'QA Team Lead');