Home T-SQL hierarchy query
Reply: 2

T-SQL hierarchy query

Ingvar
1#
Ingvar Published in 2017-12-05 18:06:43Z

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.

DhruvJoshi
2#
DhruvJoshi Reply to 2017-12-05 18:35:24Z

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

JNevill
3#
JNevill Reply to 2017-12-05 18:30:39Z

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 + CASE(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;
You need to login account before you can post.

About| Privacy statement| Terms of Service| Advertising| Contact us| Help| Sitemap|
Processed in 0.329275 second(s) , Gzip On .

© 2016 Powered by mzan.com design MATCHINFO