SQL query help

General Web Coding General Web Coding
1

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

2

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