LEJ LEJ - 11 months ago 59
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 Source
SELECT SYS_CONNECT_BY_PATH( Course_id, ' -> ' ) AS path
FROM   prerequisites
WHERE  CONNECT_BY_ISCYCLE = 1
CONNECT BY NOCYCLE
       PRIOR Course_id = Prerequisite_id;