Oracle | Find All parent recursively and return results in comma seperated

Question

I have the following table in an oracle:

P_C_REL (Child , Parent ,Ownership)

Child	Parent	Ownership
41203134	10151091	100
10250181	40001104	30
40856701	40109963	100
10159060	40001104	100
41328372	10250181	100
40001471	10159060	100
10250181	40001471	70
10151091	40856701	100
40001104	10351678	100

One child can have multiple parents and each different can lead to different path till final parent.

I need output of child, RecursiveParents, Path.

Assuming 41203134 and 41328372 are my base child for sampling purpose. I don't want to paramterize it. Child will come from different table.

Desired result #1 : get all the paths

Child	Parents	Path
41203134	41203134,10151091,40856701,40109963	Path1
41328372	41328372,10250181,40001104,10351678	Path1
41328372	41328372,10250181,40001471,10159060,40001104,10351678	Path2

Desired result #2 : get the path which having highest ownership

Child	Parents	Path
41203134	41203134,10151091,40856701,40109963	Path1
41328372	41328372,10250181,40001471,10159060,40001104,10351678	Path2

How can I do this in Oracle 19c?

This is my query:

WITH REL_TABLE(child, parent) AS
(
    SELECT  
        child, parent
    FROM    
        P_C_REL p 
    WHERE 
        parent NOT IN (SELECT child
                       FROM P_C_REL) 

    UNION ALL
 
    SELECT   
        p.child, a.parent
    FROM    
        REL_TABLE a 
    JOIN    
        P_C_REL p ON p.parent = a.child
)
SELECT *
FROM REL_TABLE
WHERE child IN (SELECT child FROM childTable)

I get this error:

ORA-32044: cycle detected while executing recursive WITH query

Answer 1

Use a hierarchical query:

SELECT CONNECT_BY_ROOT child AS child,
       SUBSTR(SYS_CONNECT_BY_PATH(child, ',' ), 2) || ',' || parent AS path
FROM   p_c_rel
WHERE CONNECT_BY_ISLEAF = 1
START WITH child IN (41203134, 41328372)
CONNECT BY child = PRIOR parent

Which, for the sample data:

CREATE TABLE P_C_REL (Child , Parent ,Ownership) AS
SELECT 41203134, 10151091, 100 FROM DUAL UNION ALL
SELECT 10250181, 40001104,  30 FROM DUAL UNION ALL
SELECT 40856701, 40109963, 100 FROM DUAL UNION ALL
SELECT 10159060, 40001104, 100 FROM DUAL UNION ALL
SELECT 41328372, 10250181, 100 FROM DUAL UNION ALL
SELECT 40001471, 10159060, 100 FROM DUAL UNION ALL
SELECT 10250181, 40001471,  70 FROM DUAL UNION ALL
SELECT 10151091, 40856701, 100 FROM DUAL UNION ALL
SELECT 40001104, 10351678, 100 FROM DUAL;

Outputs:

CHILD	PATH
41203134	41203134,10151091,40856701,40109963
41328372	41328372,10250181,40001104,10351678
41328372	41328372,10250181,40001471,10159060,40001104,10351678

Or, a recursive query:

WITH path (root, path, parent, ownership) AS (
  SELECT child, child || ',' || parent, parent, ownership
  FROM   p_c_rel
  WHERE  child IN (41203134, 41328372)
UNION ALL
  SELECT p.root, p.path || ',' || r.parent, r.parent, p.ownership + r.ownership
  FROM   path p
         INNER JOIN p_c_rel r
         ON p.parent = r.child
)
SELECT root, path, ownership
FROM   path
ORDER BY DENSE_RANK() OVER (PARTITION BY root ORDER BY ownership DESC)
FETCH FIRST ROW WITH TIES;

Which outputs:

ROOT	PATH	OWNERSHIP
41203134	41203134,10151091,40856701,40109963	300
41328372	41328372,10250181,40001471,10159060,40001104,10351678	470

fiddle