Results 1 to 2 of 2
  1. #1
    Join Date
    Feb 2004
    Posts
    21

    Unanswered: NOT IN clause in My SQL not working

    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.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •