CODEWITHSUNDEEP
mysqlsqlselect
Leonardo Lemes
Leonardo Lemes

Reputation: 77

MySQL - Selecting rows that do not have a match or that have with a condition

I can't figure this out so far, I have these tables:

I need to select the students that don't have a matching row in the stud_class table OR those who aren't in a class of a given course (the given course is a parameter).

For example: select the students that don't have any classes or those that are in a class, but not the class of the course.id = 2.

I did this so far, but it doesn't work, the query returns no rows. I know it's kind of confusing, but I don't know how to use the JOINS in this case.

SELECT  students.id, students.name 
FROM    students, stud_class, class
WHERE ( NOT (students.id = stud_class.students_id) )
  OR  ( (students.id=stud_class.students.id) AND 
        (stud_class.class_id=class.id) AND 
        ( NOT (class.course_id=2) )
      );

Upvotes: 1

Views: 1644

Answers (1)

Joel Hinz
Joel Hinz

Reputation: 25384

If I'm interpreting your question correctly, you're looking for all students that aren't in in the class of a certain course - regardless of whether they have other classes or not. In that case, does this work?

SELECT id, name FROM students
WHERE id NOT IN (
    SELECT stud_class.students_id FROM stud_class, class
    WHERE stud_class.class_id = class.id
    AND class.courses_id = 2
)

The subquery gets the ids of all students who are in the class of course 2 (as per your example), and the main query gets the ids of all students who are not in that list.

Upvotes: 2

Related Questions