Mysql eliminate user based on conditions

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 -

开发者_JAVA百科-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?

---

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')

---

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'

---

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.