Mysql eliminate user based on conditions

Question

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?

Answer 1

SELECT * FROM class_roll cl LEFT JOIN lessons_slots ls
ON cl.classid = ls.id AND ls.name != 'advanced'

So you actually want to delete rows? Well then:

DELETE FROM class_roll cl LEFT JOIN lessons_slots ls  
ON cl.classid = ls.id AND ls.name != 'advanced'

Answer 2

Not pretty, but works.

 SELECT c.*
FROM lessons_slots l
join class_roll c on l.id=c.classid
where concat(firstname,lastname) not in (select concat(firstname,lastname)
from  lessons_slots l
join class_roll c on l.id=c.classid where name='advanced')

Answer 3

You could try something to the effect of

SELECT FirstName, LastName FROM class_roll
WHERE NOT EXISTS
  (SELECT * FROM class_roll, lesson_slots 
    WHERE class_roll.classid = lesson_slots.ID
    AND lesson_slots.name = 'advanced')

You can then use the result as the basis of a DELETE query.