find records which are not matched 1

[jpadie]

sorry for the bad title. couldn't think of anything more descriptive.

I have the following tables to consider:

jobs
jobID int(11) PRIMARY
jobDate datetime

installers
installerID int(11) PRIMARY
installerName varchar(255)

jobInstallers
jobID int(11) (FK)
installeriD int(11) (FK)
(indexed as primary across the two cols)

installerHoliday
installerID int(11) FK
holidayDate datetime
(indexed as for jobInstallers)

the jobInstallers table is a many to many join table
jobDate is null in the jobs table until a job is assigned to a date
installerHoliday stores a record of which installers are off on which day

so ... what I am after is a query that delivers the installers that are free on a particular day. i.e. not on holiday and not already booked on a job. I can do this programmatically in php, though multiple queries. But I am forever advising people in the php forum to do more within their sql code. So practising what I preach, I wonder whether this can be reduced to a query?

any ideas appreciated. The table structure is simplified but hopefully i've given all you need.

Justin

 

[r937]

select i.installerID
     , i.installerName
  from installers as i
 where /* not on holiday */
       not exists      
       ( select 1
           from installerHoliday
          where installerID = i.installerID
            and holidayDate = '2007-07-19' )
   and /* not already booked on a job */
       not exists      
       ( select 1
           from jobInstallers as ji
         inner
           join jobs as j
             on j.jobID = ji.jobID
            and j.jobDate = '2007-07-19' 
          where ji.installeriD = i.installeriD )

r937.com | rudy.ca

 

[jpadie]

marvellous, Rudy. many thanks.

Justin