By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,686 Members | 2,708 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,686 IT Pros & Developers. It's quick & easy.

Query Help: SELECT Courses where student has all prerequisites

P: n/a
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
Nov 12 '05 #1
Share this Question
Share on Google+
1 Reply


P: n/a
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

Nov 12 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.