current community

your communities

more stack exchange communities

Stack Exchange
tour help
careers 2.0
Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 1 down vote favorite

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

  • students - Columns: id, name
  • stud_class - Columns: students_id, class_id
  • class - Columns: id, courses_id
  • courses - Columns: id, name

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) )
      );
share|improve this question
    
Not related to your problem, but your query uses the older ANSI-89 style joins. Might want to look into switching to ANSI-92 style JOINs instead. –  Leigh Jun 13 '13 at 2:19
add comment

1 Answer 1

up vote 1 down vote accepted

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.

share|improve this answer
    
Thanks! That's exactly what I wanted, it worked flawlessly. –  Leonardo Lemes Jun 13 '13 at 0:56
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.