Since the course level should be fairly static (well, unless the prerequisites for a course are changed), I figured I would change things a little and store the computed level. (You might laugh, but navigating your way through a maze of grad school courses where there are a bunch of course prerequisites, and all you want to know is "what course(s) can I take next?" is not a picnic!)

I just tweaked the query using the CTE a little... (I'm learning... baby steps!) So now it looks like this:

WITH PrereqsCTE (CourseID, Depth) AS

(

--- Anchor: courses without prerequisites

SELECT c.CourseID, 0 AS Lvl

FROM Course c

WHERE NOT EXISTS (SELECT * FROM Prereq WHERE NextCourseID = c.CourseID)

UNION ALL

-- Recursive member: courses that have other courses as prerequisites.

SELECT p.NextCourseID, Depth + 1

FROM Prereq p INNER JOIN PrereqsCTE

ON p.ReqCourseID = PrereqsCTE.CourseID

)

UPDATE Course

SET CourseLevel = Depth + 1

FROM Course INNER JOIN PrereqsCTE

ON Course.CourseID=PrereqsCTE.CourseID

Sure it assumes that the prerequisites are complete and stable, but that should be true.... Okay, exercise complete! Thanks for the feedback.

Never occurred to me to do nested sets (probably because I'm not enough of a math guy to even know to do that!), but the one thing I did know was that the original "map" of courses and prereqs for this degree was maybe 7 levels. (It's supposed to be a 2-year or less degree, so that kinda makes sense.) Interesting food for thought, though.

Pieter