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.