title: Using Common Table Expression for a recursive bottom up query
body: <p>I have the following table structure storing files and folder hierarchies.</p>
  
  <p><code>FolderInfo</code> table</p>
  
  <pre><code>Id (key)
  Name
  ParentFolderId (nullable)
  </code></pre>
  
  <p>and</p>
  
  <p><code>File</code> table</p>
  
  <pre><code>FileId(key)
  FileName
  FolderId
  </code></pre>
  
  <p>The <code>FolderInfo</code> table has a <code>ParentFolderId</code> which is a foreign key to its own id. The root folder has a null value in the <code>ParentFolderId</code>, all other records refer to their parent record through it.
  I want to generate a result which contains the selected files and their complete path walking up the hierarchies.</p>
  
  <p><strong>Result</strong></p>
  
  <pre><code>File
  Location
  </code></pre>
  
  <p>I need to achieve this using some smart T-SQL. I would welcome if someone can show a common table expression based approach to recursively walk the hierarchy up.</p>