Mysql eliminate user based on conditions

Posted by Dustin on Stack Overflow See other posts from Stack Overflow or by Dustin
Published on 2010-06-03T14:50:08Z Indexed on 2010/06/03 15:44 UTC
Read the original article Hit count: 335

Filed under:

I asked this last week over the weekend and it got buried in the archives before anyone could answer. So forgive me if you've already seen this.

I teach classes and want to be able to select those students who have taken one class, but not another class. I have two tables: lessons_slots which is the table for every class such as:

--------------------
-ID name slots-
-1 basics 10 -
-2 advanced 10 -
-3 basics 10 -
---------------------

The other table is class_roll, which holds enrollment info, such as:

--------------------
-sID classid firstname lastname-
-1 1 Jo Schmo
-2 1 Person Two
...
-13 2 Jo Schmo
---------------------

What I want to do, I select everyone who has not had the advanced class (for example). I've tried doing

SELECT *
FROM lessons_slots
LEFT JOIN class_roll
ON lessons_slots.ID = class_roll.classid
WHERE lessons_slots.name != 'advanced'

But that doesn't work...All it does is eliminate that row, without eliminating the user. I want Jo Schmo, for example, to not show up in the results. Any ideas?

© Stack Overflow or respective owner

Related posts about mysql-query