Please refer to the following table structure. The version being used is My SQL 4.0.1 on Windows.

The following are the create table statements of the two tables :---

DROP TABLE IF EXISTS `timeSlot`;
CREATE TABLE `timeSlot` (
`timeSlotID` varchar(20) NOT NULL default '',
`startTime` datetime NOT NULL default '0000-00-00 00:00:00',
`endTime` datetime NOT NULL default '0000-00-00 00:00:00',
`dayOfWeek` int NOT NULL default 0,
`duration` int(11) NOT NULL default 0,
`companyID` varchar(20) NOT NULL default '',
PRIMARY KEY (`timeSlotID`)
) TYPE=InnoDB;

DROP TABLE IF EXISTS `timeSlotStatus`;
CREATE TABLE `timeslotstatus` (
`appointmentID` varchar(20) NOT NULL default '',
`timeSlotID` varchar(20) NOT NULL default '',
`patientID` varchar(20) NOT NULL default '',
`staffID` varchar(20) NOT NULL default '',
`equipmentID` varchar(20) NOT NULL default '',
`providerID` varchar(20) NOT NULL default '',
`date` date NOT NULL default '0000-00-00',
`status` char(1) NOT NULL default 'A',
`companyID` varchar(20) NOT NULL default ''
) TYPE=InnoDB;

My earlier question:---

My earlier question was displaying all the free time slot ids, i.e., the time slot ids which are free for a specified date and equipment id.
Thanx a lot for the answer that I recd some days back. The following query was posted in answer to my earlier question. It displays all the free time slot ids for the specified date and equipment id. It works perfectly for a specific date. But what if I try to get the timeslotIDs not present in the timeslotstatus table but available in the timeslot table for a range of dates for the same question?

select timeslotID
, duration
from timeslot
left outer
join timeslotstatus
on timeslot.timeslotID
= timeslotstatus.timeslotID
and timeslotstatus.`date` = '2004-02-19'
and timeslotstatus.equipmentID = 1
where timeslotstatus.timeslotID is null


My current questions:---


1.

I want to display the time slot and the date for time slots which are not present in timeslot status for a specific equipment id. The equipment id would be supplied. The date can even be a range of dates.
Please refer to the table structure once again if reqd. I have already posted the table structures for the 2 tables. I want somethine like the following:---


Time Slot ID Date

2 2004-02-19
1 2004-02-19
4 2004-02-20
1 2004-02-21
3 2004-02-21

The above output indicates that these timeslot ids are free for equipment id = 1.


2.

Display all the time slot ids & all the equipment ids available for a specific date. I solved using the following statements but I would like to avoid creating a table as I have done below. Please suggest a suitable answer using one single statement (u r free to use multiple joins). Moreover, what if I specify a range of dates?

drop table if exists temp;
create table temp
select timeslot.timeslotID
from timeslot
left outer join timeslotstatus
on timeslot.timeslotID = timeslotstatus.timeslotID
and timeslotstatus.date = '2004-02-19'
where timeslotstatus.timeslotID is null;

Select temp.timeslotID,equipment.equipmentID
from temp,equipment
left outer join timeslotstatus
on temp.timeslotID = timeslotstatus.timeslotID
and timeslotstatus.equipmentID = equipment.equipmentID
where timeslotstatus.timeslotID is null and timeslotstatus.equipmentID is null
order by temp.timeslotID,equipment.equipmentID;

The output is:---


Time Slot ID Equipment ID

1 1
1 3
2 1
2 2
2 3

The output shows that time slot ids 1 & 2 are available on the date specified and out of these free slots, the equipment ids 1,3 are free for time slot id 1 and 1,2, & 3 are free for time slot id 5.

Please post the answers here or mail me at joydip_kanjilal@rediffmail.com
or
joydip_kanjilal@indiatimes.com

Thankx in advance. Please reply soon.