title: How to handle empty level in hierarchy in MS-Sql
body: <p>I have table with sample data: </p>
  
  <pre><code>+---------+--------------+---------+----------+-----------------+
  | region1 |   region2    | region3 | locality | hid             |
  +---------+--------------+---------+----------+-----------------+
  | england | bedfordshire | beford  | null     | '/1/1/1/'       |
  | england | bedfordshire | null    | arshley  | '/1/1/1/'       |
  | england | bedfordshire | bedford | kingston | '/1/1/1/1/'     |
  +---------+--------------+---------+----------+-----------------+
  </code></pre>
  
  <p>When I get the descendant of bedford, I should not get arshley, is there a way?</p>
  
  <p>If I use <code>GetLevel()&gt;3</code>, then I have to handle code for other levels also
  ie for region1 descendant..so is there any other way.</p>
  
  <p>Query Used:<br>
  Declare @hier hierarchyid<br>
  Select @hier = hid from locationtable where region3 = 'bedford' and locality is null<br>
  Select * from locationtable  where hid.IsDescendantOf(@hier)=1</p>