douhuangzhi0707
douhuangzhi0707
2016-05-02 08:28

正确使用Not In子句

已采纳

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?

  • 点赞
  • 写回答
  • 关注问题
  • 收藏
  • 复制链接分享
  • 邀请回答

2条回答

  • dpdhnd3577 dpdhnd3577 5年前

    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
       )
    
    点赞 评论 复制链接分享
  • duanmu8911 duanmu8911 5年前

    Try like this,

    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
            JOIN enroll_courses ON enroll_courses.course_id = courses.id
    WHERE   students.id = 8
            AND courses.id NOT IN ( enroll_courses.course_id );
    
    点赞 评论 复制链接分享