CODEWITHSUNDEEP
sqloracle-databasesql-in
Mr Squidr
Mr Squidr

Reputation: 143

SQL statement works with "IN" but when trying to get opposite (inversed) result with "NOT IN" it outputs everything

I'm trying to output all students that have no grades in 'NSA programming'. This same query with 'IN' instead of 'NOT IN' works perfectly for students that do have a grade in 'NSA programming' lecture,

but when I try to inverse it with 'NOT IN' query outputs all students and now just those that don't have a grade in this lecture.

SELECT oc_students.name, oc_students.last_name
FROM oc_students 
INNER JOIN oc_grades ON oc_students.id_students=oc_grades.id_students 
    WHERE oc_grades.id_lecture
    NOT IN (SELECT oc_lecturesid_lecture FROM oc_lectures WHERE lecture_name='NSA programming');

Not sure if my understanding of 'NOT IN' and 'IN' is wrong but from what I can tell this should be working as intended since I'm just reversing 'IN' statement.

If someone could tell me why this is not working and maybe show me the correct way of doing it that would be awesome.

Upvotes: 0

Views: 57

Answers (1)

Thorsten Kettner
Thorsten Kettner

Reputation: 94859

Don't join, when you only want to select certain students. Put your conditions in the WHERE clause where they belong.

Students with grades in NSA programming:

SELECT name, last_name
FROM oc_students
WHERE id_students IN
(
  SELECT id_students
  FROM oc_grades
  WHERE id_lecture =
          (SELECT id_lecture FROM oc_lectures WHERE lecture_name = 'NSA programming')
);

Students with no grade in NSA programming:

SELECT name, last_name
FROM oc_students
WHERE id_students NOT IN
(
  SELECT id_students
  FROM oc_grades
  WHERE id_lecture =
          (SELECT id_lecture FROM oc_lectures WHERE lecture_name = 'NSA programming')
);

Upvotes: 2

Related Questions