Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

How to return list of all courses for which a student meets all the prerequisites. Expand / Collapse
Author
Message
Posted Monday, August 26, 2013 5:32 PM
Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Today @ 11:24 PM
Points: 705, Visits: 4,506
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
Post #1488517
Posted Monday, August 26, 2013 7:28 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Today @ 12:05 PM
Points: 8,556, Visits: 9,047
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
Post #1488521
Posted Monday, August 26, 2013 8:48 PM
Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Today @ 11:24 PM
Points: 705, Visits: 4,506
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!
Post #1488529
Posted Tuesday, August 27, 2013 10:33 AM
Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Today @ 11:24 PM
Points: 705, Visits: 4,506
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
Post #1488856
Posted Tuesday, August 27, 2013 8:52 PM
Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Today @ 11:24 PM
Points: 705, Visits: 4,506
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.
Post #1489006
Posted Tuesday, August 27, 2013 9:55 PM
Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Today @ 11:24 PM
Points: 705, Visits: 4,506
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!
Post #1489010
Posted Wednesday, August 28, 2013 6:32 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, November 26, 2013 8:40 AM
Points: 316, Visits: 908
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

Post #1489166
Posted Wednesday, August 28, 2013 11:11 AM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Today @ 12:05 PM
Points: 8,556, Visits: 9,047
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
Post #1489338
Posted Wednesday, August 28, 2013 11:20 AM
Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Today @ 11:24 PM
Points: 705, Visits: 4,506
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
Post #1489345
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse