How to write recursive SQL query when there is cyclic relationships in table?

Question

I'm encountering the cyclic relationships while defining my recursive SQL view in Oracle SQL Developer, and I looked up a little but haven't found any valid solution to solve it.

I put an example data down here for your reference, if you can walk through this example while explaining my question, I will be really appreciated!

So the question is writing a query (by using recursion) to find all the pairs (Major, Minor) that for each of this kind of pair, Minor will be Major's direct or indirect Minor. And there will be cyclic relationships appeared. For example, you can have a cycle as: P1 -> P4 -> P2 -> P3 -> P1 -> P4 ...

Update: I should make my problem more clear. I am trying to learn a way to write a legit recursive SQL query (without using CYCLE/NO CYCLE) to solve this problem.

And the result I expected should be roughly as the following:

If there is no cyclic relationship exists, I will use the query as the following:

WITH PartsPair(ma, mi) AS (
  (SELECT Major as ma, Minor as mi FROM Parts)
UNION ALL
  (SELECT a1.Major, a2.Minor
   FROM PartsPair a1, Parts a2
   WHERE a1.Minor = a2.Major)
)
SELECT *
FROM PartsPair;

But now there is cyclic relationships, so is there any way to add small changes to my above query without using CYCLE, NO CYCLE to solve this problem?

Answer 1

Use NOCYCLE in your hierarchical query:

SELECT CONNECT_BY_ROOT( major ) AS major,
       CONNECT_BY_ROOT( major ) || SYS_CONNECT_BY_PATH( minor, ',' ) AS path,
       minor,
       quantity
FROM   table_name
CONNECT BY NOCYCLE PRIOR minor = major

Which, for your sample data:

CREATE TABLE table_name ( major, minor, quantity ) AS
SELECT 'p1', 'p2', 2 FROM DUAL UNION ALL
SELECT 'p1', 'p3', 3 FROM DUAL UNION ALL
SELECT 'p1', 'p4', 2 FROM DUAL UNION ALL
SELECT 'p2', 'p3', 3 FROM DUAL UNION ALL
SELECT 'p3', 'p5', 2 FROM DUAL UNION ALL
SELECT 'p3', 'p1', 3 FROM DUAL UNION ALL
SELECT 'p4', 'p2', 4 FROM DUAL;

Outputs all the majors and the paths to the possible minors that can be reached (without endlessly following cycles):

MAJOR | PATH              | MINOR | QUANTITY
:---- | :---------------- | :---- | -------:
p1    | p1,p2             | p2    |        2
p1    | p1,p2,p3          | p3    |        3
p1    | p1,p2,p3,p5       | p5    |        2
p1    | p1,p2,p3,p1       | p1    |        3
p1    | p1,p2,p3,p1,p4    | p4    |        2
p1    | p1,p3             | p3    |        3
p1    | p1,p3,p5          | p5    |        2
p1    | p1,p3,p1          | p1    |        3
p1    | p1,p3,p1,p2       | p2    |        2
p1    | p1,p3,p1,p4       | p4    |        2
p1    | p1,p3,p1,p4,p2    | p2    |        4
p1    | p1,p4             | p4    |        2
p1    | p1,p4,p2          | p2    |        4
p1    | p1,p4,p2,p3       | p3    |        3
p1    | p1,p4,p2,p3,p5    | p5    |        2
p1    | p1,p4,p2,p3,p1    | p1    |        3
p2    | p2,p3             | p3    |        3
p2    | p2,p3,p5          | p5    |        2
p2    | p2,p3,p1          | p1    |        3
p2    | p2,p3,p1,p2       | p2    |        2
p2    | p2,p3,p1,p4       | p4    |        2
p2    | p2,p3,p1,p4,p2    | p2    |        4
p3    | p3,p5             | p5    |        2
p3    | p3,p1             | p1    |        3
p3    | p3,p1,p2          | p2    |        2
p3    | p3,p1,p2,p3       | p3    |        3
p3    | p3,p1,p2,p3,p5    | p5    |        2
p3    | p3,p1,p3          | p3    |        3
p3    | p3,p1,p3,p5       | p5    |        2
p3    | p3,p1,p4          | p4    |        2
p3    | p3,p1,p4,p2       | p2    |        4
p3    | p3,p1,p4,p2,p3    | p3    |        3
p3    | p3,p1,p4,p2,p3,p5 | p5    |        2
p4    | p4,p2             | p2    |        4
p4    | p4,p2,p3          | p3    |        3
p4    | p4,p2,p3,p5       | p5    |        2
p4    | p4,p2,p3,p1       | p1    |        3
p4    | p4,p2,p3,p1,p4    | p4    |        2

If you just want the shortest paths to a minor then:

SELECT major,
       path,
       minor,
       quantity
FROM   (
  SELECT CONNECT_BY_ROOT( major ) AS major,
         CONNECT_BY_ROOT( major ) || SYS_CONNECT_BY_PATH( minor, ',' ) AS path,
         minor,
         quantity,
         DENSE_RANK() OVER (
           PARTITION BY CONNECT_BY_ROOT( major ), minor
           ORDER BY LEVEL ASC
         ) AS rank_by_depth
  FROM   table_name
  CONNECT BY NOCYCLE
         PRIOR minor = major
)
WHERE  rank_by_depth = 1
ORDER BY major, minor;

Which outputs:

MAJOR | PATH           | MINOR | QUANTITY
:---- | :------------- | :---- | -------:
p1    | p1,p3,p1       | p1    |        3
p1    | p1,p2          | p2    |        2
p1    | p1,p3          | p3    |        3
p1    | p1,p4          | p4    |        2
p1    | p1,p3,p5       | p5    |        2
p2    | p2,p3,p1       | p1    |        3
p2    | p2,p3,p1,p2    | p2    |        2
p2    | p2,p3          | p3    |        3
p2    | p2,p3,p1,p4    | p4    |        2
p2    | p2,p3,p5       | p5    |        2
p3    | p3,p1          | p1    |        3
p3    | p3,p1,p2       | p2    |        2
p3    | p3,p1,p3       | p3    |        3
p3    | p3,p1,p4       | p4    |        2
p3    | p3,p5          | p5    |        2
p4    | p4,p2,p3,p1    | p1    |        3
p4    | p4,p2          | p2    |        4
p4    | p4,p2,p3       | p3    |        3
p4    | p4,p2,p3,p1,p4 | p4    |        2
p4    | p4,p2,p3,p5    | p5    |        2

db<>fiddle here

---

If you just want the distinct minors reachable from a major then:

SELECT DISTINCT
       CONNECT_BY_ROOT( major ) AS major,
       minor
FROM   table_name
WHERE  CONNECT_BY_ROOT( major ) <> minor
CONNECT BY NOCYCLE PRIOR minor = major
ORDER BY major, minor;

Which outputs:

MAJOR | MINOR
:---- | :----
p1    | p2   
p1    | p3   
p1    | p4   
p1    | p5   
p2    | p1   
p2    | p3   
p2    | p4   
p2    | p5   
p3    | p1   
p3    | p2   
p3    | p4   
p3    | p5   
p4    | p1   
p4    | p2   
p4    | p3   
p4    | p5   

You can do the same with a recursive sub-query factoring clause but you also need to include a CYCLE expression for that method:

WITH PartsPair(major, minor) AS (
  SELECT Major, Minor
  FROM   table_name
UNION ALL
  SELECT a1.Major, a2.Minor
  FROM   PartsPair a1
         INNER JOIN table_name a2
         ON a1.Minor = a2.Major
)
CYCLE major, minor SET is_cycle TO 1 DEFAULT 0
SELECT DISTINCT major, minor
FROM   PartsPair
WHERE  major <> minor
ORDER BY major, minor;

Which outputs the same as the previous query.

db<>fiddle here