Get entire hierarchy of values in a sql table consisting of two columns

Question

I have the following table:

+-----------+------------+
| ChildCode | ParentCode |
+-----------+------------+
| AUSNZ     |   APACZ    |
| EASIAZ    |   APACZ    |
| SEASIAZ   |   APACZ    |
| AUSTR     |   AUSNZ    |
| NZ        |   AUSNZ    |
| PACISZ    |   AUSNZ    |
| TOGO      |   PACISZ   |
+-----------+------------+

I would like to have all ChildCodes with the ParentCode APACZ. However, the problem is that the ChildCode again can be a ParentCode (see AUSNZ) - there are multiple layers. Anyone an idea how to get all ChildCodes/ParentCodes belonging to APACZ, i.e. to get the entire list of Codes which have APACZ as a root?

Thanks in advance!

sorry, had to figure out how to get a table in stackoverflow. — Lejahn, Aug 27 '18 at 11:24
[This page](https://ozh.github.io/ascii-tables/) can make quick work for formatting table data for your question. — Dan Guzman, Aug 27 '18 at 11:28
Your question is not clear, a desired outputs will be much more better. — Ilyes, Aug 27 '18 at 12:04
Look up recursive query. You will find this often for supervisory roles as well as menus. — KeithL, Aug 27 '18 at 12:22

score 0 · Answer 1 · answered Aug 27 '18 at 13:54

This is a straightforward recursive CTE. There are many examples on stackoverflow. In your case, if you just want the list of children, you could do something like this....

;WITH list_structure AS
(
SELECT
    l.Child_Code,
    l.Parent_Code
    FROM [YOUR_TABLE_NAME_OF_VALUES_HERE] l
    WHERE l.Parent_Code ='APACZ'
UNION ALL
SELECT
    l.child_Code,
    l.Parent_Code
    FROM list_hierarchy l
    INNER JOIN list_structure
    ON l.Parent_Code = list_structure.child_Code
)

SELECT Child_Code FROM list_structure

Get entire hierarchy of values in a sql table consisting of two columns

1 Answers1