How to get students who have NOT attended?

[benphp]

I have 3 tables:

 

classes CID|Cdate

students SID|LastName|FirstName

classes_students CLID|SID|CID

 

I'm trying to get the students who have NOT attended a class since 2009-01-01. I've managed to kludge together something with if statements, but I'd like to know how to do it properly in SQL.

 

I can write the SQL to get positive results, but I can't figure out how to get negative results. Any help is appreciated!

 

[fenway]

LEFT JOIN.

[benphp]

I can get the positive - that is, students who do have a record after a certain date.

 

Select DISTINCT
students.SID,
students.LastName,
students.FirstName
FROM 
classes_students 
LEFT JOIN classes ON (classes_students.CLID=classes.CLID)
LEFT JOIN students ON (classes_students.SID=students.SID)
WHERE
classes.Cdate > '2009-01-01'

 

However, I can't figure out how to get students who have NO record for dates beyond 2009-01-01.

 

 

[benphp]

I think I figured it out.

 

Select DISTINCT
students.SID,
students.LastName,
students.FirstName
FROM 
classes_students as cs
LEFT JOIN classes ON (cs.CLID=classes.CLID)
LEFT JOIN students ON (cs.SID=students.SID)
WHERE
students.EID NOT IN 
(SELECT DISTINCT 
	classes_students.EID 
FROM classes_students 
LEFT JOIN classes ON (students.CLID=classes.CLID) 
WHERE classes.CDate > '2010-01-01' )
ORDER BY students.LastName ASC

[fenway]

You needed "IS NULL", not a NOT IN.

[benphp]

It seems to work though. How would you use IS NULL? I tried a few different ways but got nothin

[kickstart]

Hi

 

Using NOT IN is less efficient, but in this case also unnecessary.

 

Select students.SID,
students.LastName,
students.FirstName
FROM students
LEFT OUTER JOIN classes_students AS cs ON cs.SID=students.SID
LEFT OUTER JOIN classes ON (cs.CLID=classes.CLID AND classes.CDate > '2010-01-01' )
WHERE classes.CLID IS NULL
ORDER BY students.LastName ASC

 

All the best

 

Keith