title: SQL query to get full hierarchy path from different tables
body: <p>This is very relevant to the question <a href="https://stackoverflow.com/q/3727543/93202">SQL query to get full hierarchy path</a>.</p>
  
  <p>The only difference is that my hierarchy contains different entity types from different tables, for example:</p>
  
  <ol>
  <li>Project</li>
  <li>Phase</li>
  <li>Folder</li>
  <li>Document</li>
  </ol>
  
  <p>I want to select all documents along with their full paths (from poject level to folder level). Phases and folders can have subphases and folders.</p>
  
  <p>I'm horrible at SQL and just can't figure out how to adapt the answer to the linked question to suit my needs.</p>
  
  <p><strong>Data model and example data</strong></p>
  
  <pre><code>DECLARE @Project TABLE (ID INTEGER, Name VARCHAR(32))
  DECLARE @Phase TABLE (ID INTEGER, Project_ID INTEGER, Parent_Phase_ID INTEGER, Name VARCHAR(32))
  DECLARE @Folder TABLE (ID INTEGER, Phase_ID INTEGER, Parent_Folder_ID INTEGER, Name VARCHAR(32))
  DECLARE @Document TABLE (ID INTEGER, Folder_ID INTEGER, Name VARCHAR(32))
  
  INSERT INTO @Project VALUES (1, 'MyProject')
  
  INSERT INTO @Phase
    SELECT 1, 1, 0, 'MyPhase1'
    UNION ALL SELECT 2, 1, 1, 'MyPhase1_1'
    UNION ALL SELECT 3, 1, 0, 'MyPhase2'
  
  INSERT INTO @Folder
    SELECT 1, 1, 0, 'MyFolder'
    UNION ALL SELECT 2, 1, 1, 'MySubFolder'
    UNION ALL SELECT 3, 1, 0, 'AnotherFolder'
  
  INSERT INTO @Document
    SELECT 1, 2, 'MyDocument'
    UNION ALL SELECT 2, 3, 'AnotherDocument'
  </code></pre>
  
  <p><strong>Example</strong></p>
  
  <pre><code>MyProject1 ................ Project
  - MyPhase1 ................ Phase
    - MyPhase1_1 ............ Phase
      - MyFolder ............ Folder
        - MySubfolder ....... Folder
          - MyDocument ...... Document
      - AnotherFolder ....... Folder
        - AnotherDocument ... Document
  - MyPhase2 ................ Phase
  </code></pre>
  
  <p>Ideal query results in this case:</p>
  
  <pre><code>Document        | Path
  MyDocument      | MyProject1/MyPhase1/MyPhase1_1/MyFolder/MySubfolder  
  AnotherDocument | MyProject1/MyPhase1/MyPhase1_1/AnotherFolder
  </code></pre>