Oracle SQL Find Circular Prerequisites

Question

I have two tables as defined below:

    Course(Worker_id, Course_id)
          Primary key is (Worker_id, Course_id)

    Prerequisite(Course_id, Prerequisite_id)
          Primary key is (Course_id, Prerequisite_id)

I would like to be able to find all circular prerequisites. For example, if CourseA was a prerequisite for CourseB and CourseB was a prerequisite for CourseA (simplified example). My result should be the name of the courses that have unsatisfiable prerequisites, in this case both CourseA and CourseB.

For cases that can have more than 2 courses in the cycle, see [this answer](http://stackoverflow.com/a/5803666/4479309) for a solution. — Boneist, Nov 29 '16 at 16:36

score 3 · Accepted Answer · answered Nov 29 '16 at 16:21

3

SELECT SYS_CONNECT_BY_PATH( Course_id, ' -> ' ) AS path
FROM   prerequisites
WHERE  CONNECT_BY_ISCYCLE = 1
CONNECT BY NOCYCLE
       PRIOR Course_id = Prerequisite_id;

answered Nov 29 '16 at 16:21

MT0

143,790
11
59
117

that doesn't work if the cycle has more than two items in it (eg. course A requires course B which requires course C which requires course A). Or rather, it'll give you the start and end of the cycle, but not any values in between - ie. course B wouldn't be reported as being in a cycle. – Boneist Nov 29 '16 at 16:34
seems to work fine for me? includes every course that is part of the cycle – LEJ Nov 29 '16 at 16:40
@Boneist - sys_connect_by_path will list all nodes along the path. – Nov 29 '16 at 22:24

Oracle SQL Find Circular Prerequisites

1 Answers1