How to find find data that DOESNT match

[Muncher]

I thought this was going to be a simple query, but i cant get the results i want from the query i am trying, please help.

 

Sample Data (3 tables)

Student Table

Student ID

Student Name

6239

Billy

6240

Linda

6241

Sam

 

ENROLLMENT TABLE

Student ID

Class ID

6239

1

6239

2

6239

3

6240

1

 

CLASS TABLE

Class ID

Class Name

1

Chemistry

2

English

3

Art

 

So from these three tables you can see that

Billy (6239) is enrolled in all 3 class

Linda (6240) is enrolled in Chemistry only

Sam (6241) is not enrolled in any classes at all.

 

The query i am trying to make is to find Sam, the student who is in my Student Table but not enrolled in any classes.

 

I thought the query would be something like this but of course didn't work...

 

SELECT *

FROM StudentTable, EnrollmentTable

WHERE StudentTable.StudentID != EnrollmentTable.StudentID

 

i also tried (but the same garbage results)

SELECT *

FROM StudentTable, EnrollmentTable

WHERE StudentTable.StudentID NOT LIKE EnrollmentTable.StudentID

 

 

so basically i am looking for and StudentID's from the Student Table that have no matches in the Enrollment Table.

 

Can anybody help please

 

[Barand]

use LEFT JOIN

 

see

 

http://www.phpfreaks.com/tutorial/data-joins-unions

[fenway]

Try this:

 

SELECT StudentTable.*
FROM StudentTable
LEFT JOIN EnrollmentTable USING ( StudentID )
WHERE EnrollmentTable.StudentID IS NULL