title: Multi-Table Hierarchy Query
body: <p>I am trying to create a hierarchical query in SQL Server.  </p>
  
  <p>I have 4 tables:</p>
  
  <ul>
  <li>org3 (org3_id, org3_name)</li>
  <li>org4 (org4_id, org4_name, org4_org3id)</li>
  <li>org5 (org5_id, org5_name, org5_org4id)</li>
  <li>org6 (org6_id, org6_name, org6_org5id)</li>
  </ul>
  
  <p><code>Org3</code> is the highest level and org 6 is the lowest. As you can see org6 has a parent id in the org5 table, which has a parent id in the org4 table, which has a parent id in the org3 table.</p>
  
  <p>It is possible for a parent to not have any children.</p>
  
  <p>So say I have these values in the tables:</p>
  
  <p><strong>Org3</strong> table</p>
  
  <pre><code>| org3_id | org3_name |
  +---------+-----------+
  | 1       | MS        |
  | 2       | NS        |
  </code></pre>
  
  <p><strong>Org4</strong> table</p>
  
  <pre><code>| org4_id | org4_name | org4_org3id |
  +---------+-----------+-------------+
  | 1       | TS        | 1           |
  | 2       | QS        | 1           |
  | 3       | BS        | 1           |
  </code></pre>
  
  <p><strong>Org5</strong> table</p>
  
  <pre><code>| org5_id | org5_name | org5_org4id |
  +---------+-----------+-------------+
  | 1       | LS        | 1           |
  | 2       | PS        | 1           |
  | 3       | VS        | 2           |
  </code></pre>
  
  <p><strong>Org6</strong> table</p>
  
  <pre><code>| org6_id | org6_name | org6_org5id |
  +---------+-----------+-------------+
  | 1       | AS        | 1           |
  | 2       | RS        | 1           |
  | 3       | ZS        | 2           |
  </code></pre>
  
  <p>The result I would like to get is:</p>
  
  <pre><code>| org3_id | org3_name | org4_id | org4_name | org5_id | org5_name | org6_id | org6_name | path        |
  |---------|-----------|---------|-----------|---------|-----------|---------|-----------|-------------|
  | 1       | MS        | NULL    | NULL      | NULL    | NULL      | NULL    | NULL      | MS          |
  | 1       | MS        | 1       | TS        | NULL    | NULL      | NULL    | NULL      | MS\TS       |
  | 1       | MS        | 1       | TS        | 1       | LS        | NULL    | NULL      | MS\TS\LS    |
  | 1       | MS        | 1       | TS        | 1       | LS        | 1       | AS        | MS\TS\LS\AS |
  | 1       | MS        | 1       | TS        | 1       | LS        | 2       | RS        | MS\TS\LS\RS |
  | 1       | MS        | 1       | TS        | 2       | PS        | NULL    | NULL      | MS\TS\PS    |
  | 1       | MS        | 1       | TS        | 2       | PS        | 3       | ZS        | MS\TS\PS\ZS |
  | 1       | MS        | 2       | QS        | NULL    | NULL      | NULL    | NULL      | MS\QS       |
  | 1       | MS        | 2       | QS        | 3       | VS        | NULL    | NULL      | MS\QS\VS    |
  | 1       | MS        | 3       | BS        | NULL    | NULL      | NULL    | NULL      | MS\BS       |
  | 2       | NS        | NULL    | NULL      | NULL    | NULL      | NULL    | NULL      | NS          |
  </code></pre>
  
  <p>This is what I have tried.</p>
  
  <pre><code>SELECT 
      org3.org3_id,
      org3.org3_name,
      org3.org3_open_ind,
      org4.org4_id,
      org4.org4_name,
      org4.org4_open_ind,
      org5.org5_id,
      org5.org5_name,
      org5.org5_open_ind,
      org6.org6_id,
      org6.org6_name,
      org6.org6_open_ind,
      CONCAT(org3.org3_abbrv, '\', org4.org4_abbrv, 
             CASE
                WHEN org5.org5_abbrv IS NULL THEN ''
                ELSE CONCAT('\', org5.org5_abbrv)
             END, 
             CASE
                WHEN org6.org6_abbrv IS NULL THEN ''
                ELSE CONCAT('\', org6.org6_abbrv)
             END) AS [ORG PATH]                       
  FROM 
      (SELECT
           *
       FROM
           TSTAFFORG3 
       WHERE
           org3_open_ind = 1) org3
  LEFT OUTER JOIN 
      (SELECT
           *
       FROM
           TSTAFFORG4 
       WHERE
           org4_open_ind = 1) org4 ON org4.org4_org3id = org3.org3_id
  LEFT OUTER JOIN 
      (SELECT
           *
       FROM
           TSTAFFORG5
       WHERE
           org5_open_ind = 1) org5 ON org5.org5_org4id = org4.org4_id
  LEFT OUTER JOIN 
      (SELECT
           *
       FROM 
           TSTAFFORG6
       WHERE
           org6_open_ind = 1) org6 ON org6.org6_org5id = org5.org5_id
  ORDER BY
      org3.org3_name, org4.org4_name, org5.org5_name, org6.org6_name
  </code></pre>
  
  <p>I think maybe a CTE query is needed, but I am not sure how to frame it in this case. If it was all in one table I think I could figure it out, but because it is multiple tables, I am having trouble figuring out the SQL. The query I tried doesn't show just the parent. It will only show where results where org3 has children.</p>