Here's a small subset of the data... that's actually intelligible. Apologies for any SQL errors.

CREATE TABLE #Course (

CourseID CHAR(7),

CONSTRAINT pkCourse PRIMARY KEY (CourseID));

CREATE TABLE #Prereq (

NextCourseID CHAR(7),

RequiresCourseID CHAR(7),

CONSTRAINT pkPrereq PRIMARY KEY (NextCourseID, RequiresCourseID)),

FOREIGN KEY NextCourseID REFERENCES Course(CourseID),

RequiresCOurseID REFERENCES Course(CourseID);

INSERT INTO #Course (CourseID) VALUES ('503'); -- no prereqs

INSERT INTO #Course (CourseID) VALUES ('601'); -- no prereqs

INSERT INTO #Course (CourseID) VALUES ('605');

INSERT INTO #Course (CourseID) VALUES ('608');

INSERT INTO #Course (CourseID) VALUES ('620');

INSERT INTO #Prereq (NextCourseID, RequiresCourseID) VALUES ('605','601');

INSERT INTO #Prereq (NextCourseID, RequiresCourseID) VALUES ('605','503');

INSERT INTO #Prereq (NextCourseID, RequiresCourseID) VALUES ('608','503');

INSERT INTO #Prereq (NextCourseID, RequiresCourseID) VALUES ('620','605');

INSERT INTO #Prereq (NextCourseID, RequiresCourseID) VALUES ('620','608');

CREATE TABLE Grades (

StudentID INT,

CourseID CHAR(7),

TermYear CHAR(7).

Score TINYINT CHECK BETWEEN 0 AND 4,

PRIMARY KEY (StudentID, CourseID, TermYear),

FOREIGN KEY StudentID REFERENCES Student(StudentID),

CourseID REFERENCES Course(CourseID)

);

So, to sanity check this...

If a student has no courses completed, he may take 601 and 503.

If a student has passed both 605 AND 608, he may enroll in 620. Having passed only one is not enough. This is where I am getting stuck.

It would seem that I should be able to outer join Prereqs to Scores and filter out the fails and then if there are any unmatched records, the prerequisites are not met.

IF EXISTS (SELECT 1

FROM Prereqs P LEFT JOIN Scores S ON P.RequiresCourseID = S.CourseID

WHERE Grade IS NULL OR Grade<2)

but that doesn't take into account that a student could fail a course and then pass it on the retake...

Ouch... I think I brained my damage!

More later when I'm coherent...

thanks!