SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


How to return list of all courses for which a student meets all the prerequisites.


How to return list of all courses for which a student meets all the prerequisites.

Author
Message
pietlinden
pietlinden
SSCarpal Tunnel
SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)

Group: General Forum Members
Points: 4836 Visits: 13160
Sorry for the lame title. This is somewhat of a follow-on from a previous post, which is here http://www.sqlservercentral.com/Forums/Topic1488185-3077-1.aspx It deals with the same tables: Student, Course, Prerequisite, Grades.

I was trying to work out a reasonable way to return the list of courses a student met all the prerequisites to take, and am close (I think... but then I've been wrong before!) and was looking for a few ideas. This is part of the way there, as it returns True/False for courses for which a student meets all the prerequisites.

DECLARE @NextCourseID CHAR(7)='605';
DECLARE @StudentID INT = 1;
-- it would make sense to do a (NOT) EXISTS to check for missing values
IF EXISTS ( SELECT ReqCourseID, G.Grade
FROM Prereq P LEFT JOIN Grades G ON P.ReqCourseID = G.CourseID
WHERE NextCourseID = @NextCourseID
AND G.StudentID = @StudentID
AND G.Grade>=2 /* 2.0 here is a passing grade */)
BEGIN
PRINT 'True'
END
ELSE
BEGIN
PRINT 'False'
END



(Sorry it's incomplete... one step at a time!) Should I follow SQLKiwi's article on "Understanding Apply", as I think that will solve the problem. I guess it's just totally uncharted territory for me.

The other way I was thinking of trying to do it was to outer join Prereqs to Courses, then eliminate any courses where the grade for the prerequisite course either NULL or not a pass.

So how would you approach this problem? (no need to do it for me... I need to learn to do it myself!)

Thanks!
Pieter
TomThomson
TomThomson
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14240 Visits: 12197
You have something close to the core of it there, because something like the subquery in the EXISTS expression is a good starting point. But it's going to print TRUE if any prerequisite course is passed, even if some prerequisites are failed or not yet taken.

The first thing to do is to throw away everything but that EXISTS expression; then change EXISTS to NOT EXISTS (as per your comment) and change
G.Grade >= 2.0 to IsNULL(G.Grade,0) < 2, because you will be using that clause to check for prerequisite courses which were failed or have not been taken, and change the subquery so that it begins SELECT 1 instead of SELECT ReqCourseID, G.Grade because you are just checking for existence, not returning values.

The second thing to do is to make It look at all the courses which might be the student's next, and list only those for which he is qualified: so in front of the NOT EXISTS clause you write SELECT @StudentId, C.CourseID from Course C WHERE and in the subquery change @NextCourseID to C.CourseID. Now you will have something that lists all the courses the student is qualified to enter; but it has a flaw: it lists courses the student has already passed as well as ones he hasn't yet passed. So you need to add AND followed by another NOT EXISTS expression to the WHERE clause. The new NOT EXIsts expression has to have a subquery which will select courses that the student has already passed. And that's it.

Once you are there, it is fairly easy to change the outermost select so that it uses StudentID from the student table and replace @studentid in the query by studentId, then add an ORDER BY StudentID, CourseID clause to guarantee that all data for a student comes out on adjacent lines rather than being scattered amongst data for other students; or to add some pretty formatting if you want it.

Tom

pietlinden
pietlinden
SSCarpal Tunnel
SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)

Group: General Forum Members
Points: 4836 Visits: 13160
So negate everything in parentheses, and then wrap a NOT around it (double negatives, my favorite!)

Makes sense... now, let me see if I can actually make it work!

Thanks!
pietlinden
pietlinden
SSCarpal Tunnel
SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)

Group: General Forum Members
Points: 4836 Visits: 13160
okay, now to do something sane and create a subset of the two tables and make sure it works -- looks like it does, but I'm not sure yet. It compiles and runs, which is a lot further than I got by myself.

Thanks for taking the time to walk me through it! It was a BIG help! (Especially the correlated subquery parts and the inverted logic.)

Pieter
pietlinden
pietlinden
SSCarpal Tunnel
SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)

Group: General Forum Members
Points: 4836 Visits: 13160
I must have munged the SQL somewhere... it's not omitting the "next" courses for which a given student has some but not all of the prerequisites.

Here's the SQL I have, which runs, but returns the wrong answer...

DECLARE @StudentID INT = 1;

SELECT @StudentId AS StudentID
, C.CourseID
FROM Course C
WHERE NOT EXISTS ( SELECT 1
FROM Prereq P LEFT JOIN Grades G ON P.ReqCourseID = G.CourseID
WHERE NextCourseID = C.CourseID
AND G.StudentID = @StudentID
AND IsNULL(G.Grade,0) < 2
)
AND NOT EXISTS (
SELECT 1
FROM Grades
WHERE Grade>=2 -- 2 is considered passing.
AND StudentID = @StudentID
AND CourseID = C.CourseID)



Sorry... I'll come back to this and add some minimal number of records. The hard part is picking SOME of the course hierarchy (I'll start at the bottom.). So I'll work on that and post hopefully by tomorrow night. Then I can figure out what's going on.
pietlinden
pietlinden
SSCarpal Tunnel
SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)

Group: General Forum Members
Points: 4836 Visits: 13160
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!
Stefan_G
Stefan_G
SSC-Addicted
SSC-Addicted (497 reputation)SSC-Addicted (497 reputation)SSC-Addicted (497 reputation)SSC-Addicted (497 reputation)SSC-Addicted (497 reputation)SSC-Addicted (497 reputation)SSC-Addicted (497 reputation)SSC-Addicted (497 reputation)

Group: General Forum Members
Points: 497 Visits: 960
I took your sample code, and cleaned it up a bit.

This is the solution I came up with:

set nocount on

CREATE TABLE #Course (
CourseID CHAR(7)
);

CREATE TABLE #Prereq (
NextCourseID CHAR(7),
RequiresCourseID CHAR(7)
);

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
);

insert into #Grades (StudentID, CourseID, TermYear, Score)
select 1, 601, 2013, 2 union all
select 1, 503, 2013, 2 union all
select 2, 601, 2013, 2

declare @student int = 1

select *
from #Course c
-- where there does not exist any prerequsite that has not been passed by this student
-- and the student has not already passed the course
where not exists(
-- Any prereq for this course that has not been passed by this student
select *
from #Prereq p
where
p.NextCourseID = c.CourseID
and
p.RequiresCourseID not in (select CourseID from #Grades where StudentID = @student and Score >= 2)
)
and
-- exclude courses already passed by this student
not exists(select * from #Grades g where g.StudentID = @student and Score >= 2 and g.CourseID = c.CourseID)


drop table #Course
drop table #Prereq
drop table #Grades


TomThomson
TomThomson
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14240 Visits: 12197
Stefan's version caters for retakes too, because it looks for the absence of success rather than the presence of failure - a nice and tidy way to handle retakes.

Tom

pietlinden
pietlinden
SSCarpal Tunnel
SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)

Group: General Forum Members
Points: 4836 Visits: 13160
Cool! Thanks, Stefan!

Now to walk through it slowly (with the explanations) and see if I can learn to spot the pattern.

I think the fun part is the NOT EXISTS (... NOT IN ()) stuff, which seems funny.... well, I guess until you're used to it!

Mission accomplished! Thanks everybody!

Pieter
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search