title: How to get child data from a self referencing table in sql
body: <p>I am using sql 2012 and I have a self referencing table that have 3 levels. The table structure is as below:</p>
  
  <p>Table Structure</p>
  
  <p><img src="https://i.stack.imgur.com/VEnfZ.png" alt=""></p>
  
  <p>I have another table that is referencing this table, it have ID as a foreign key, so if the foreign key to that table is 6, I need to show that 6 is "AAA" and it is the child node of "AA" which is the child node of "A". I need to drill down up to the lower level and from the lower level I should be able to go up to the upper level. Currently I can go up to the second level.</p>
  
  <p>Below is the structure of the table that references the other table.
  <img src="https://i.stack.imgur.com/kk53f.png" alt=""></p>
  
  <p>So I would like to report on both these table and the final output should look like this :
  <img src="https://i.stack.imgur.com/QgmVF.png" alt=""></p>
  
  <p>If my question is not too clear please ask I will try and clarify it.</p>