How can I pair a bottom value in a hierarchy with each level above it?

Question

I'm working with an SQL Server database and I have the following two tables:

Nested Apps                                        Nested App Groups
|---------------------|------------------|         |---------------------|------------------|
|       App_Group     |        App       |         |       App_Group     | Child_App_Group  |
|---------------------|------------------|         |---------------------|------------------|
|          5          |         A        |         |          1          |         4        |
|---------------------|------------------|         |---------------------|------------------|
|          7          |         B        |         |          4          |         5        |
|---------------------|------------------|         |---------------------|------------------|
|          9          |         C        |         |          2          |         6        |
|---------------------|------------------|         |---------------------|------------------|
                                                   |          6          |         7        |
                                                   |---------------------|------------------|
                                                   |          3          |         8        |
                                                   |---------------------|------------------|
                                                   |          8          |         9        |
                                                   |---------------------|------------------|

On the left we can see the parent/child relation between App Groups and Apps. On the right we can see the parent/child relation between App Groups and Apps Groups (Apps can not have children). In the example App A is a child of App Group 5, which is a child of App Group 4, which then is a child of App Group 1.

I want a table that shows the nested parent/child relation of App Groups and Apps, regardless of whether the App is a direct child or a "grandchild", etc... Like this:

|---------------------|------------------|
|       App_Group     |        App       |
|---------------------|------------------|
|          1          |         A        |
|---------------------|------------------|
|          4          |         A        |
|---------------------|------------------|
|          5          |         A        |
|---------------------|------------------|
|          2          |         B        |
|---------------------|------------------|
|          6          |         B        |
|---------------------|------------------|
|          7          |         B        |
|---------------------|------------------|
|          3          |         C        |
|---------------------|------------------|
|          8          |         C        |
|---------------------|------------------|
|          9          |         C        |
|---------------------|------------------|

I've not really come close to solving it. I've tried to work with the idea that I somehow have to run through the hierarchy which would be a bit easier if I had one App at a time. But here I have to do it with three Apps at once. I have no idea how to even begin... Anyone?

UPDATE:

I've come very close with something like this:

WITH cte AS ( SELECT app_group_id, app_id FROM nested_apps UNION ALL SELECT
nag.app_group_id, c.app_id FROM nested_app_groups nag JOIN cte c ON
nag.child_app_group_id = c.app_group_id ) SELECT app_group_id, app_id FROM
cte

Does this answer your question? [How to create a MySQL hierarchical recursive query](https://stackoverflow.com/questions/20215744/how-to-create-a-mysql-hierarchical-recursive-query) — philipxy, Apr 24 '20 at 10:43
This is a faq. Before considering posting please read the manual & google any error message or many clear, concise & precise phrasings of your question/problem/goal, with & without your particular strings/names & site:stackoverflow.com & tags; read many answers. If you post a question, use one phrasing as title. Reflect your research. See [ask] & the voting arrow mouseover texts. — philipxy, Apr 24 '20 at 10:43
Please in code questions give a [mre]--cut & paste & runnable code, including smallest representative example input as code; desired & actual output (including verbatim error messages); tags & versions; clear specification & explanation. Give the least code you can give that is code that you show is OK extended by code that you show is not OK. (Debugging fundamental.) For SQL that includes DBMS & DDL (including constraints & indexes) & input as code formatted as a table. — philipxy, Apr 24 '20 at 10:44

score 0 · Answer 1 · edited Jul 21 '20 at 18:21

I think you can build query using CTE (recursively and using temporary table) like this:

Resource:

CTE Microsoft: https://learn.microsoft.com/fr-fr/sql/t-sql/queries/with-common-table-expression-transact-sql?view=sql-server-ver15 Link: https://blog.sqlauthority.com/2012/04/24/sql-server-introduction-to-hierarchical-query-using-a-recursive-cte-a-primer/

create Table #APPS
(
    App_Group varchar(128),
    App varchar(128)
)

Insert into #APPS
(
    App_Group,
    App
)

SELECT '5', 'A'
UNION ALL 
SELECT '7','B'
UNION ALL 
SELECT '9','C'

create Table #APPS_GROUPS
(
    App_Group varchar(128),
    Child_App_Group varchar(128)
)

Insert into #APPS_GROUPS
(
    App_Group,
    Child_App_Group 
)

SELECT '1', '4'
UNION ALL 
SELECT '4','5'
UNION ALL 
SELECT '2','6'
UNION ALL 
SELECT '6','7'
UNION ALL 
SELECT '3','8'
UNION ALL 
SELECT '8','9'



SELECT *
INTO #MYAPPS
FROM (
    SELECT AG.App_Group, AG.Child_App_Group, '' AS 'App'
    FROM #APPS_GROUPS AG
    UNION ALL 
    SELECT A.App_Group, '' AS 'Child_App_Group', App AS 'App'
    FROM #APPS A
) SUBQUERY
ORDER BY App;

WITH MyCteAPP AS (
    SELECT App_Group, Child_App_Group, App
    FROM #MYAPPS
    WHERE Child_App_Group = ''
    UNION ALL 
    SELECT MYAPP.App_Group, MYAPP.Child_App_Group, FAPP.App
    FROM #MYAPPS MYAPP
    JOIN MyCteAPP FAPP ON MYAPP.Child_App_Group = FAPP.App_Group
    WHERE MYAPP.Child_App_Group <> ''
)
SELECT App_Group, App
FROM MyCteAPP
ORDER BY App

DROP TABLE  #APPS
DROP TABLE  #APPS_GROUPS
DROP TABLE #MYAPPS

Hello, thank you for your answer. The problem with your answer is that the code is not generic. I'm not supposed to hardcode the values in the code like "SELECT '1', '4'" in your example. The values is not always 1 and 4, etc. But I've tried something like this: `WITH cte AS ( SELECT app_group_id, app_id FROM nested_apps UNION ALL SELECT nag.app_group_id, c.app_id FROM nested_app_groups nag JOIN cte c ON nag.child_app_group_id = c.app_group_id ) SELECT app_group_id, app_id FROM cte` But I'm not sure if it covers all scenarios of a hierarchy.. — TheStranger, Apr 24 '20 at 12:47
yes it's generic because have a recursion throw the cte (like join cte). i think it done what your're tryyng todo. look at this link : https://blog.sqlauthority.com/2012/04/24/sql-server-introduction-to-hierarchical-query-using-a-recursive-cte-a-primer/ — Sanpas, Apr 24 '20 at 12:52
@Benji if it answer to your query please upvote or put as accepted answer for tag post — Sanpas, Apr 24 '20 at 13:20

How can I pair a bottom value in a hierarchy with each level above it?

UPDATE:

1 Answers1