title: Best method to search hierarchical data
body: <p>I'm looking at building a facility which allows querying for data with hierarchical filtering. I have a few ideas how I'm going to go about it but was wondering if there are any recommendations or suggestions that might be more efficient.</p>
  
  <p>As an example imagine that a user is searching for a job. The job areas would be as follows.</p>
  
  <pre><code>1: Scotland
  2: --- West Central
  3: ------ Glasgow
  4: ------ Etc
  5: --- North East
  6: ------ Ayrshire
  7: ------ Etc
  </code></pre>
  
  <p>A user can search specific (i.e. Glasgow) or in a larger area (i.e. Scotland).</p>
  
  <p>The two approaches I am considering are:</p>
  
  <ol>
  <li>keep a note of children in the database for each record (i.e. cat 1 would have 2, 3, 4 in its children field) and query against that record with a <code>SELECT * FROM Jobs WHERE Category IN Areas.childrenField</code>.</li>
  <li>Use a recursive function to find all results who have a relation to the selected area.</li>
  </ol>
  
  <p>The problems I see from both are:</p>
  
  <ol>
  <li>Holding this data in the db will mean having to keep track of all changes to structure.</li>
  <li>Recursion is slow and inefficent.</li>
  </ol>
  
  <p>Any ideas, suggestion or recommendations on the best approach? I'm using C# ASP.NET with MSSQL 2005 DB.</p>