title: T-SQL hierarchy query
body: <p>I have a table with hierarchical data: 
  <a href="https://i.stack.imgur.com/8Or62.png" rel="noreferrer"><img src="https://i.stack.imgur.com/8Or62.png" alt="enter image description here"></a></p>
  
  <p>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.</p>
  
  <p>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:</p>
  
  <ul>
  <li>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</li>
  <li>Rows from 1 till 5 should not be returned because code was mentioned somewhere in the hierarchy.</li>
  </ul>
  
  <p>How should one solve this problem with T-SQL?</p>
  
  <p>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.</p>