Query Help: SELECT Courses where student has all prerequisites

[Pieter Linden]

Hi,
I think the subject line pretty much says it all...

Say I have a students-classes database and I add a twist. I want to
filter what courses a student can take by comparing the courses he has
taken to the list of prerequisites for the remaining courses.
Everything is fine if a course only has one or no prerequisite. How
do you express in SQL something like:

"Course C" requires "Course A" and "Course B"

and then leave out all the students who do not have both Course A and
Course B?

What "Course C" requires is easy:

CREATE TABLE RequiresCourse(
Course INT NOT NULL,
Prereq INT NOT NULL,
PRIMARY KEY (Course, Prereq));

SELECT Prereq
FROM RequiresCourse
WHERE Prereq = 'Course C';

but how do I determine who has *all* the different prerequisites for a
course?

No. it's not a homework assignment. Honest. Could be and should be,
but it isn't. I'll read my books anyway... I'm dying to figure this
stupid thing out!

Thanks!
Pieter

[Allen Browne]

Peter, it may help to think backwards.

This query produces a list of the prerequisites a student has NOT completed
for a course. If no records are returned, the student is eligible.

SELECT tblCoursePrerequisite.PrerequisiteID
FROM tblCoursePrerequisite LEFT JOIN tblResult
ON tblCoursePrerequisite.PrerequisiteID = tblResult.CourseID
WHERE (tblCoursePrereqisite.ForCourseID = 99) AND NOT EXISTS
(SELECT CourseID FROM tblResult
WHERE (tblResult.StudentID = 999) AND
(tblResult.CourseID = tblCoursePrerequisite.PrerequisiteID) );

Structure assumed:
tblCourse (list of courses):
CourseID as primary key.

tblCoursePrerequisite:
ForCourseID CourseID that has a prerequisite.
PrerequisiteID CourseID that is a prerequisite.
Two one-to-many joins to tblCourse, so a course can have many prerequisites.

tblResult (student results in their courses):
StudentID foreign key to tblStudent.StudentID
CourseID foreign key to tblCourse.CourseID
Result what they achieved
ResultDate when achieved

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Pieter Linden" <pi********@hotmail.com> wrote in message
news:bf**************************@posting.google.c om...

Hi,
I think the subject line pretty much says it all...

Say I have a students-classes database and I add a twist. I want to
filter what courses a student can take by comparing the courses he has
taken to the list of prerequisites for the remaining courses.
Everything is fine if a course only has one or no prerequisite. How
do you express in SQL something like:

"Course C" requires "Course A" and "Course B"

and then leave out all the students who do not have both Course A and
Course B?

What "Course C" requires is easy:

CREATE TABLE RequiresCourse(
Course INT NOT NULL,
Prereq INT NOT NULL,
PRIMARY KEY (Course, Prereq));

SELECT Prereq
FROM RequiresCourse
WHERE Prereq = 'Course C';

but how do I determine who has *all* the different prerequisites for a
course?

No. it's not a homework assignment. Honest. Could be and should be,
but it isn't. I'll read my books anyway... I'm dying to figure this
stupid thing out!

Thanks!
Pieter