CODEWITHSUNDEEP
sqlsqlite
blue
blue

Reputation: 11

SQL query works but very slow on large data set?

Trying to make a query that selects courses to take next quarter that you have already taken the prerequisite for. What makes it difficult for me is if there are multiple prerequisites for a course and you have already completed one.

The two tables are Record(Student, Course) and Prerequisite(Course, Prereq)

My query attempt that works but very slow:

select r.Student, p.Course 
from Record r, Prerequisite p where r.Course = p.Prereq and p.Course not in
( select Course from (select r.Student, p.Course from Record r, Prerequisite p 
  where r.Course <> p.Prereq) a where a.Student = r.Student);

Any help would be great!

Sample data

CREATE TABLE Prerequisite (Course CHAR(32), Prereq CHAR(32));
CREATE TABLE Record (
    Student CHAR(32), 
    Course CHAR(32),
    PRIMARY KEY (Student, Course)
);
INSERT INTO Prerequisite VALUES ('Class 3', 'Class 1');
INSERT INTO Prerequisite VALUES ('Class 6', 'Class 4');
INSERT INTO Prerequisite VALUES ('Class 4', 'Class 1');
INSERT INTO Prerequisite VALUES ('Class 4', 'Class 3');
INSERT INTO Prerequisite VALUES ('Class 8', 'Class 2');
INSERT INTO Prerequisite VALUES ('Class 8', 'Class 6');
INSERT INTO Prerequisite VALUES ('Class 5', 'Class 2');
INSERT INTO Prerequisite VALUES ('Class 9', 'Class 8');
INSERT INTO Prerequisite VALUES ('Class 7', 'Class 5');
--added Class 10 requires Class 1 and Class 2
INSERT INTO Prerequisite VALUES ('Class 10', 'Class 1');
INSERT INTO Prerequisite VALUES ('Class 10', 'Class 2');

INSERT INTO Record VALUES ('Bob', 'Class 1');
INSERT INTO Record VALUES ('Jan', 'Class 2');
--Bob Takes both Class 1 and Class 2 thus he can take Class 10
INSERT INTO Record VALUES ('Bob', 'Class 2');

So result should be 

Bob | Class 3
Jan | Class 5
Bob | Class 5
Bob | Class 10

edit: small sample data added

edit2: see the comments below for additional details

edit3: Just realized my solution is not working while looking over Mhai's new solution and making a test case that showed it wasn't working, mine didn't work for that case either.

Updated the sample data with a new test case, that is when you have a student that has taken both prerequisites for a class, they should take that class.

Upvotes: 1

Views: 167

Answers (2)

Mihai
Mihai

Reputation: 26784

Try

SELECT r.Student, p.Course FROM Record r
JOIN Prerequisite p
ON r.Course = p.Prereq
LEFT JOIN (
     select  r.Student, p.Course FROM Record r
     JOIN Prerequisite p 
     ON p.Prereq<>r.Course)x
ON x.Student = r.Student
WHERE p.Course IS NULL

SELECT  r.Student,p.Course,pp.Prereq FROM Record r
JOIN Prerequisite p
ON r.Course = p.Prereq
JOIN Prerequisite pp
ON
p.Course=pp.Course
GROUP BY p.Course HAVING COUNT(*)=1

This will find all records with just 1 requirement

Fiddle

Upvotes: 2

Santhosh_ms3
Santhosh_ms3

Reputation: 110

select r.Student, p.Course 
from Record r inner join Prerequisite p on p.Prereq = r.course where not exists(
( select pr.Course from Prerequisite pr inner join records rr on rr.student=r.student 
where pr.Prereq =r.course ))

Try this, use NOLOCK if you afford to have dirty records.

Upvotes: 0

Related Questions