title: SQL Server 2008 Hierarchical queries
body: <p>I have a table <code>Locations(id, type, code, parentid)</code>.</p>
  
  <p>The type can be COUNTRY/STATE/REGION/TOWN. So this table holds hierarchical geographic data.
  Country will have many states, state many regions and regions many towns.</p>
  
  <p>I need to list out descendent records for a given parent record. For example I need all records under Country Australia. The parent record in this case will be </p>
  
  <pre><code>10, COUNTRY, AU, null)
  </code></pre>
  
  <p>I understand a recursive CTE is the way to go but not sure how to.</p>
  
  <p>thanks</p>