LEJ LEJ - 8 days ago 5
SQL Question

Oracle SQL Find Circular Prerequisites

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.

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