Query to display fields that meet a criteria

[jd307]

The subject isn't very descriptive of the issue that I am having to over come.  Basically, the problem is part of the timetabling system that I am building.  The criteria states I must write a query in SQL (this is for an MS Access 2003 database, so not exactly MySQL) that can list available and suitable tutors.  Basically, the idea is that if a tutor is unavailable for a session they are going to teach (i.e they have called in sick), the coordinator must find another tutor.  As it is a music school, the tutor must be able to play the instrument for the class, but must not be teaching another lesson.

 

Here is what I have so far:

SELECT     RTRIM(tutor.firstName)+' '+RTRIM(tutor.lastName) AS Tutor_Name, tutor_skill.level, startTime
FROM       tutor, tutor_skill, instrument_type, tutor_availability, tutor_session, session
WHERE     tutor.tutorID = tutor_skill.tutorID
AND          tutor_skill.instrumentTypeID = instrument_type.instrumentTypeID
AND          instrumentType = "guitar"
AND          tutor.tutorID = tutor_availability.tutorID
AND          termID = 11
AND          tutor.tutorID = tutor_session.tutorID
AND          tutor_session.sessionID = session.sessionID
AND          startTime<>#15:0:0#

 

This works perfectly for what I want.  It displays the tutor name, their skill level (in the stated instrument, guitar in this case) and start times of their sessions.  From this statement it shows ALL tutors who are available during the term (term 11), and who can play the guitar.  As the session is at 3pm, it removes all tutors who have lessons at 3pm (as per the last line).

 

The problem is, John Smith has lessons at 9am, 12pm and 3pm.  This query removes the record for John Smith at 3pm but still shows him in the list (with the times of 9am and 12pm.  What I want to do is say "IF tutor has a session at #15:0:0# then do not display him at all"  instead of "show all occurances of this tutor, except for the time stated", which is what it is doing. 

 

Any ideas how this can be achieved, purely in SQL.

 

Thanks.

[fenway]

Sounds like you need to put this into your ON clause... with 6 tables, you should really be using proper join syntax.