user3044500 user3044500 - 4 months ago 9
SQL Question

Selecting records from a table not in another table

I have two tables.

Course
course_id | name
------------------
1 | PROG
2 | ENGL
3 | SCIE

Enrollment List
ID | student_id | course_id | grade
-----------------------------------
1 | 445566 | 1 | 4.0
2 | 445566 | 2 | 2.0
3 | 778899 | 3 | 2.5


I need to query the tables such that it returns the student_id and the courses they haven't taken yet. Outcome should be:

student_id | course_id
----------------------
445566 | 3
778899 | 1
778899 | 2


I tried the query

SELECT student_id, name FROM course c, list l WHERE NOT EXISTS(SELECT NULL FROM course c, list l WHERE c.course_id=l.course_id)


which returned zero records. How would I do this?

Answer

Simple rule: Never use commas in the FROM clause. Always use explicit JOIN syntax.

In any case, you need to approach this in a particular way. Start with a list of all students and all courses. Then use left join or not exists to filter out the ones that are not in the list:

SELECT s.student_id, c.name
FROM course c CROSS JOIN
     (SELECT DISTINCT student_id FROM list l) s
WHERE NOT EXISTS (SELECT 1
                  FROM list l2
                  WHERE c.course_id = l2.course_id and s.student_id = l2.student_id
                 )