SQL query help

[skypanther]

Can someone help me with a query that's eluding me? I have these three tables:

classes
=================
classID (index, autonumber)
classTitle (varchar 255)
etc. ...

library
=================
fileID (index, autonumber)
fileName (varchar 255)
fileUploadDate (varchar 30)

libraryfiles
=================
classID
fileID

The classes table lists training events. The library table records files that could be associated with one or more classes. The libraryfiles table relates the two so that I can record all the "library" files associated with a given class. So, there can be zero or more entries in the libraryfiles table per class.

Selecting which library files are assigned to a given class is easy. What I need to do is find all the library files that are not assigned to a given class.

I thought I was close with:

select distinct libraryfiles.fileID, library.fileName
FROM libraryfiles, library
WHERE libraryfiles.classID <> {classID}
AND libraryfiles.fileID = library.fileID

(Where {classID} is dynamically filled in by my PHP script.)

But, if for example file ID 3 is assigned to class 1 and class 2, the query above fails. File ID 3 would meet the query's criteria and be included, even though it's already assigned to the class and should thus be excluded.

Suggestions?

Thanks!
Tim

 

[skypanther]

Nevermind, I got it with the help of a very kind guy on a SQL forum elsewhere.

SELECT library.fileID, fileName
FROM library
LEFT JOIN libraryfiles ON library.fileID=libraryfiles.fileID
AND libraryfile.classID = {$_GET['classID']}
WHERE libraryfiles.fileID IS NULL

Thanks,
Tim

(The problem required subqueries, which aren't supported by MySQL versions older than about 4.1, hence the laborious join syntax used here.)