Results 1 to 8 of 8
  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;

  2. #2
    Join Date
    Feb 2004
    Location
    Ninth Hell
    Posts
    19
    Both queries in your select are correct, they run perfectly on their own
    but as a whole it seems to choke up.

    I tried the following query with only a couple of test rows and it seems to work ok:

    Code:
    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`))
    try it and let me know.

  3. #3
    Join Date
    Oct 2003
    Location
    Slovakia
    Posts
    482

    Re: NOT IN clause in My SQL not working

    Originally posted by joydip_kanjilal
    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;
    Subselects (Nested queries) are supported in > 4.1.x only, And as you said you have 4.0.1!!!
    Little advice to the future: Read manuals...

  4. #4
    Join Date
    Feb 2004
    Posts
    21
    Originally posted by death2all
    Both queries in your select are correct, they run perfectly on their own
    but as a whole it seems to choke up.

    I tried the following query with only a couple of test rows and it seems to work ok:

    Code:
    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`))
    try it and let me know.

    I have tried but I am not getting the correct result. I require a select to be inside the NOT IN clause. Please suggest a suitable solution using joins as select in NOT IN is not supported by the version I am using. Please take some test data and re-check. Thanx in advance.


  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    what is wrong?

    you are trying to use a subquery, which isn't supported until 4.1

    try this instead:
    PHP Code:
    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 
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Feb 2004
    Posts
    21
    Thanx a lot for the answer. The following query was posted in answer to my 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


    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? 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

    Please reply soon.

  7. #7
    Join Date
    Feb 2004
    Posts
    21

    Please reply soon. V.V.Urgent.

    Thanx a lot for the answer. The following query was posted in answer to my 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


    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? 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

    Please reply soon.

  8. #8
    Join Date
    Feb 2004
    Posts
    21
    Originally posted by r937
    what is wrong?

    you are trying to use a subquery, which isn't supported until 4.1

    try this instead:
    PHP Code:
    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




    Thanx a lot 
    for the answerThe following query was posted in answer to my 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


    It works perfectly 
    for a specific dateBut what if 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 questionPlease refer to the table structure once again if reqdI have already posted the table structures for the 2 tablesI 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

    Please reply soon


Posting Permissions

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