I'm using oracle sql and I got into a problem.

I have this table:

I need to get new table that for each parent it will show their entire children like this:

Desired Query Output:

The tree: (for explanation of the question only)

Here is what I have tried:

SELECT *
FROM TABLE_1
START WITH PARENT_ID IS NULL
CONNECT BY PRIOR ID = PARENT_ID;

Another thing I have tried:

 SELECT connect_by_root(parent_id) "PARENT", id "CHILD"
 FROM   table1
 WHERE  connect_by_root(parent_id) is not null
 CONNECT BY parent_id = prior id

Both selections are not working for what I have wanted. thanks.

CodePudding user response：

Your query looks OK - partially; when reused, it returns desired result. Read comments within code.

Sample data:

SQL> with test (id, name, parent_id) as
  2    (select 1, 'A'   ,  null from dual union all
  3     select 2, 'A1'  , 1     from dual union all
  4     select 3, 'A2'  , 1     from dual union all
  5     select 4, 'A3'  , 1     from dual union all
  6     select 5, 'A11' , 2     from dual union all
  7     select 6, 'A12' , 2     from dual union all
  8     select 7, 'A21' , 3     from dual union all
  9     select 8, 'A121', 6     from dual union all
 10     select 9, 'A122', 6     from dual
 11    ),

Query begins here:

 12  temp as
 13    -- your query, with additional columns (child_name, lvl)
 14    (select connect_by_root (a.parent_id) parent,
 15            a.id child,
 16            --
 17            a.name child_name,
 18            level lvl
 19     from test a
 20     where connect_by_root(a.parent_id) is not null
 21     connect by a.parent_id = prior a.id
 22    )
 23  -- finally, join result of your query to sample data to extract parent name
 24  select a.name parent_name,
 25         t.child_name
 26  from test a join temp t on a.id = t.parent
 27  order by parent_name, lvl, child_name;

Result:

PARENT_NAME CHILD_NAME
----------- -----------
A           A1
A           A2
A           A3
A           A11
A           A12
A           A21
A           A121
A           A122
A1          A11
A1          A12
A1          A121
A1          A122
A12         A121
A12         A122
A2          A21

15 rows selected.

SQL>

CodePudding user response：

You can use:

SELECT  CONNECT_BY_ROOT name AS ancestor,
        name AS child
FROM    table1
WHERE   LEVEL > 1
CONNECT BY
        parent_id = prior id

Which, for the sample data:

CREATE TABLE table1 (ID, name, parent_id) AS
SELECT 1, 'A',    NULL FROM DUAL UNION ALL
SELECT 2, 'A1',   1 FROM DUAL UNION ALL
SELECT 3, 'A2',   1 FROM DUAL UNION ALL
SELECT 4, 'A3',   1 FROM DUAL UNION ALL
SELECT 5, 'A11',  2 FROM DUAL UNION ALL
SELECT 6, 'A12',  2 FROM DUAL UNION ALL
SELECT 7, 'A21',  3 FROM DUAL UNION ALL
SELECT 8, 'A121', 6 FROM DUAL UNION ALL
SELECT 9, 'A122', 6 FROM DUAL;

Outputs:

ANCESTOR	CHILD
A1	A11
A1	A12
A1	A121
A1	A122
A2	A21
A12	A121
A12	A122
A	A1
A	A11
A	A12
A	A121
A	A122
A	A3
A	A2
A	A21

db<>fiddle here