Home T-SQL hierarchy query
Reply: 0

T-SQL hierarchy query

user871
1#
user871 Published in July 17, 2018, 7:57 am

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.

You need to login account before you can post.

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

© 2016 Powered by mzan.com design MATCHINFO