What is wrong with the following query?

Select timeslotID,duration from timeslot where timeslot.timeslotID NOT IN (Select timeslotstatus.timeslotID from timeslotstatus where date='2004-02-19' and equipmentID=1);

The tables used are:
timeslot & timeslotstatus


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;


The following is the select statement that I recd from dbforums.

SELECT
`timeslot`.`timeSlotID`,
`timeslot`.`duration`
FROM
`timeslot`,
`timeslotstatus`
WHERE
(`timeslotstatus`.`equipmentID` = 1) AND
(`timeslotstatus`.`date` = '2004-02-19') AND
(`timeslot`.`timeSlotID` NOT IN (`timeslotstatus`.`timeSlotID`))


This is not returning the desired results. What I require is that a selct query to be inside the not in clause.

As an example, the statement select timeslotID from timeslot would produce the following ids:--

1
2
3
4
5



whereas the outer select would produce suppose only the following:---

2
4
5



Hence, the output that I require is the ids not present in timslot.

1
3


This is my objective.

I came to know from a post that NOT IN using subselect is not suported in My SQL 4.0.1 What then is the equivalent query statement using joins? Please reply urgently.

For this query, you probably want to do it in two steps. Use this code:

I have tried the query but it is not providing any output. Please check again.

two steps? no, not necessary

try this

There is just a little problem with that query. MySQL gives an error: timeslotID is ambiguous. Just add the table name 'timeslot' to the beginning of timeslotID in the SELECT list and everything works great.

good catch!

i can't test every piece of sql i offer, and this was one i didn't

That's understandable. Well, I only thought of testing the date and equipment id in the WHERE clause, so that's why I put the solution if two steps. Yours is much cleaner.