Home T-SQL hierarchy query

# 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#
 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#
 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.
Processed in 0.329275 second(s) , Gzip On .