Re: appointments availability

[Peter Brawley]

CREATE TABLE `avail_apps` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`date_av` date NOT NULL,
`time_av` time DEFAULT NULL,
PRIMARY KEY (`id`,`date_av`)
) ENGINE=InnoDB AUTO_INCREMENT=7135 DEFAULT CHARSET=utf8

CREATE TABLE `appointments` (
...
`startDate` date NOT NULL,
`startime` time NOT NULL,
`endDate` date NOT NULL,
`endTime` time NOT NULL
) ENGINE=InnoDB AUTO_INCREMENT=16 DEFAULT CHARSET=utf8

Assuming appointments has a primary k apptID, and leaving aside for the moment the possibility of appts that begin one day and end on another day ...

select ...
from avail_apps a
left join appointments p on a.date_av=p.startdate and a.time_av between p.starttime and p.endtime
where p.apptid is null;

Suggestion: forget appointments.enddate and endtime, add duration in minutes or whatever matches likely work patterns.