Please refer to the following table structure.

Appointment Table

CREATE TABLE `appointment` (
`tableID` varchar(12) NOT NULL default '',
`startTimeSlotID` varchar(12) NOT NULL default '',
`endTimeSlotID` varchar(12) NOT NULL default '',
`patientID` varchar(12) NOT NULL default '',
`staffID` varchar(12) NOT NULL default '',
`equipmentID` varchar(12) NOT NULL default '',
`date` date NOT NULL default '0000-00-00',
`status` varchar(12) NOT NULL default '',
`isDeleted` char(1) NOT NULL default 'N',
`companyID` varchar(12) NOT NULL default ''
) TYPE=InnoDB;



Equipment Master Table

CREATE TABLE `equipment` (
`equipmentID` varchar(12) NOT NULL default '',
`tableID` varchar(12) NOT NULL default '',
`equipmentTypeID` varchar(12) NOT NULL default '',
`purchaseDate` date NOT NULL default '0000-00-00',
`purchaseTime` time NOT NULL default '00:00:00',
`seller` varchar(100) NOT NULL default '',
`runningCycleTime` int(11) NOT NULL default '0',
`setupTime` int(11) NOT NULL default '0',
`isDeleted` char(1) NOT NULL default 'N',
`companyID` varchar(20) NOT NULL default '',
PRIMARY KEY (`equipmentID`)
) TYPE=InnoDB;



Time Slot Table



CREATE TABLE `timeslot` (
`timeSlotID` varchar(20) NOT NULL default '',
`tableID` varchar(12) NOT NULL default '',
`startTime` time NOT NULL default '00:00:00',
`endTime` time NOT NULL default '00:00:00',
`dayOfWeek` int(11) NOT NULL default '0',
`duration` int(11) NOT NULL default '0',
`isDeleted` char(1) NOT NULL default 'N',
`companyID` varchar(12) NOT NULL default '',
PRIMARY KEY (`timeSlotID`,`tableID`,`startTime`,`endTime`,`day OfWeek`)
) TYPE=InnoDB;

I tried the following query for a specific date. It worked perfectly.

Select timeslot.timeslotID
from timeslot
left outer join appointment
on timeslot.timeslotID between appointment.startTimeslotID and appointment.endTimeslotID
and appointment.date = '2004-02-19' and appointment.equipmentID= 1 and appointment.companyID = 1
where appointment.startTimeslotID is null;

This works perfectly for the date specified and displays all the available slots for one date only.

The problem is that I am unable to use another join. I would like to get all the available time slot ids for a range of date. A time slot is available if
it is present in the time slot master but not present/allocated
in the appointment table.

Let the appointment table contain the following records. I have selected only the fields that are reqd. Please ignore the other fields of appointment table.



Appointment Table


timeslotidstart timeslotidend date equipment id
1 1 19/02/2004 2
4 5 19/02/2004 1
3 5 20/02/2004 1



The output that is reqd would be:---



OUTPUT


Date Time Slot

02/19/2004 1
02/19/2004 2
02/19/2004 3
02/20/2004 1
02/20/2004 2

Note that time slot ids 1,2,3 are free on 19th, 1,2 are free on 20th. The
time slot id 1 is allocated to equipment id 2 in the appointment table.

Thankx in advnce.