-2

I have a database with a Student table and a Course table with an M to M relationship. I need to select all students that are not taking a specific course. How can I do this sort of negative condition?

Kevin
  • 11
  • 2
  • Can you post your table definitions? Use a `LEFT JOIN` of the `Student` to `Course` table, and retain student records which do _not_ match to any course. – Tim Biegeleisen Oct 08 '15 at 01:37
  • How would you select all students that *are* taking a specific course? – philipxy Oct 08 '15 at 01:38
  • [Not](https://dev.mysql.com/doc/refman/5.1/en/logical-operators.html#operator_not). – Jeremy Fortune Oct 08 '15 at 01:41
  • How are you tracking which courses are being taken by which students and / or vice versa? Are you using an intermediate table or some other technique? – toonice Oct 08 '15 at 01:53
  • Please give as much information as possible about your situation & what you have tried. Please read the links [here](http://meta.stackoverflow.com/) about asking questions.. – philipxy Oct 08 '15 at 02:12

3 Answers3

0

There are a few ways:

use an outer join:

select s.student_id
  from student s
  left join course c
    on s.student_id = c.student_id
   and c.course_id = 'xyz'
 where c.student_id is null;

use a not exists subquery:

select student_id
  from student s
 where not exists (select 1
          from course c
         where c.student_id = s.student_id
           and c.course_id = 'xyz');

use a not in subquery:

select student_id
  from student
 where student_id not in (select student_id from course where course_id = 'xyz');
Brian DeMilia
  • 13,103
  • 1
  • 23
  • 33
0

You could do this with

  • NOT EXISTS and a correlated subquery; i.e., select all students where there does not exist a relationship to the specific course

  • IN and an uncorrelated subquery; i.e., select all students that are not in the set of students that have a relationship to the specific course

  • OUTER JOIN and a filter where the course name is NULL; i.e. select all students and their relationship, if any, to the specific course, and filter out the ones that are taking the course.

and probably other methods.

Dave Costa
  • 47,262
  • 8
  • 56
  • 72
0

Are you looking for this query ?

    SELECT * FROM Student a WHERE a.M NOT IN (SELECT b.M FROM Course b WHERE)
japzdivino
  • 1,736
  • 3
  • 17
  • 25