CODEWITHSUNDEEP
mysqlsql
user3044500
user3044500

Reputation: 47

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?

Upvotes: 1

Views: 41

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269543

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
                 )

Upvotes: 3

Related Questions