SQL problem

[Albatross]

http://sqlfiddle.com/#!2/17bd34/158

So i need to select Bob and Jan with class 1 and 2 respectively.
But class1 demands courses 3 and 4 and course 4 has prerequisites class 1 and 3 so i need to select only course 3 which has one prerequisite which is class 1.

Same for Jan:class 2 demands courses 5 and 8 but 8 also demands class 6 so only class 5 should be selected.
So these are expected results:

Bob | Class 3
Jan | Class 5

I only managed to somehow isolate the problem now I dont know how to continue so that the query will work for people with multiple requirements also,because I could do a GROUP BY HAVING COUNT(*)=1 and this will get my results but this is an accident.Thanks.

 

[douglasb]

You could have another table for just the prerequisite courses.

So, for your example:

Class  | Prerequisite
-------+-------------
4      |  1
4      |  3
3      |  1
5      |  2
8      |  6
8      |  5

Assuming that you have a table with each student and the classes that he/she has already taken, then you can see which classes have all of the prerequisites filled for a particular student.

EDIT: I may have misunderstood your question. Looking at the fiddle, it looks like you already have that covered.

 

[KB]

Your schema needs to be a bit more complicated. I think the simple schema actually makes the problem more difficult.

You need a lot more tables, plus identity rows, foriegn keys etc.

CREATE TABLE Student (StudentID int, Name CHAR(50));
CREATE TABLE Course (CourseID int, Name CHAR(50));
CREATE TABLE StudentCourse (StudentCourseID int, CourseID int, StudentID int);
CREATE TABLE Prerequisite (CoursePreReqsID int, CourseID int, PreCourseID int);

INSERT INTO Student VALUES (1, 'Bob');
INSERT INTO Student VALUES (2, 'Jan');

INSERT INTO Course VALUES(1, 'Class 1');
INSERT INTO Course VALUES(2, 'Class 2');
INSERT INTO Course VALUES(3, 'Class 3');
INSERT INTO Course VALUES(4, 'Class 4');
INSERT INTO Course VALUES(5, 'Class 5');
INSERT INTO Course VALUES(6, 'Class 6');
INSERT INTO Course VALUES(7, 'Class 7');
INSERT INTO Course VALUES(8, 'Class 8');
INSERT INTO Course VALUES(9, 'Class 9');

INSERT INTO StudentCourse VALUES(1, 1, 1);
INSERT INTO StudentCourse VALUES(2, 2, 2);

INSERT INTO Prerequisite VALUES(1, 3, 1);
INSERT INTO Prerequisite VALUES(2, 6, 4);
INSERT INTO Prerequisite VALUES(3, 4, 1);
INSERT INTO Prerequisite VALUES(4, 4, 3);
INSERT INTO Prerequisite VALUES(5, 8, 2);
INSERT INTO Prerequisite VALUES(6, 8, 6);
INSERT INTO Prerequisite VALUES(7, 5, 2);
INSERT INTO Prerequisite VALUES(8, 9, 8);
INSERT INTO Prerequisite VALUES(9, 7, 5);

Heres the ugly query. Someone might be able tosimplify it, but it is a rather difficult problem for SQL. SQL likes to match things, not find unmatched things.

SELECT Course.CourseID, Course.Name, Student.StudentID, Student.Name
FROM     Course INNER JOIN
                  Prerequisite ON Course.CourseID = Prerequisite.CourseID LEFT JOIN
                  StudentCourse ON Prerequisite.PreCourseID = StudentCourse.CourseID LEFT JOIN
                  Student ON StudentCourse.StudentID = Student.StudentID LEFT JOIN
				  (SELECT Course.CourseID, Course.Name As Course, s.StudentID, s.Name
					FROM     Course INNER JOIN
                  Prerequisite ON Course.CourseID = Prerequisite.CourseID LEFT JOIN
                  StudentCourse ON Prerequisite.PreCourseID = StudentCourse.CourseID LEFT JOIN
                  Student ON StudentCourse.StudentID = Student.StudentID CROSS JOIN Student s
				  WHERE Student.StudentID IS NULL) Incomplete ON Student.StudentID = Incomplete.StudentID AND Course.CourseID = Incomplete.CourseID
WHERE Incomplete.CourseID IS NULL AND Student.StudentID IS NOT NULL

 

[douglasb]

^ Agree on the schema being too simple. You could write a query for the simple schema in the 1st fiddle and it would work, but it would be a nightmare.

 

[Albatross]

Thank you all.I agree that is an autistic schema but it`s not mine,it`s a problem I encountered online so I was curious if anyone could solve it.