正确使用Not In子句

Question

Here, the scenario is:

I have 4 tables

1. Course
2. Student
3. Department
4. EnrollCourse

While a student going to enroll a course a list of courses should be in dropdown from courses table. But course_id which are already in enroll_courses table shouldn't load in that courses dropdown. Because a student can't register single course 2 times. Here I'm using not in operation which will retrieve list of course_id which are not in enroll_courses table.

But I'm getting following error for the query:

You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'enroll_courses.course_id LIMIT 0, 25' at line 6

Here is query:

SELECT students.name, students.email,departments.name as d_name,
        courses.name as c_name
        FROM students JOIN departments on students.department_id=departments.id 
        JOIN courses on departments.id = courses.department_id
        LEFT JOIN enroll_courses on enroll_courses.course_id=courses.id 
        WHERE courses.id NOT IN (enroll_courses.course_id) AND students.id=8

Anyone who can help me to find the solution?

Answer 1

There are several ways to get the desired result, this is close to your original query:

SELECT  students.name ,
        students.email ,
        departments.name AS d_name ,
        courses.name AS c_name
FROM    students
JOIN departments
  ON students.department_id = departments.id
JOIN courses
  ON departments.id = courses.department_id
WHERE students.id = 8   
-- up to here it's all possible courses

-- now remove already enrolled courses
  AND courses.id NOT IN  
   (
     SELECT course_id
     FROM enroll_courses
     WHERE student_id = 8
   )

Or a Correlated version:

  AND courses.id NOT IN  
   (
     SELECT course_id
     FROM enroll_courses
     WHERE student_id = students.student_id
   )

Or NOT EXISTS:

  AND NOT EXISTS 
   (
     SELECT *
     FROM enroll_courses
     WHERE student_id = students.student_id
       AND course_id = courses.course_id
   )