August 26, 2013 at 5:32 pm
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[/url]", 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
August 26, 2013 at 7:28 pm
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
August 26, 2013 at 8:48 pm
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!
August 27, 2013 at 10:33 am
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
August 27, 2013 at 8:52 pm
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.
August 27, 2013 at 9:55 pm
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!
August 28, 2013 at 6:32 am
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
August 28, 2013 at 11:11 am
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
August 28, 2013 at 11:20 am
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
September 29, 2023 at 10:37 am
This was removed by the editor as SPAM
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply