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

    Unanswered: What is the equivalent of NOT IN using sub select clause in My SQL ?

    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.

  2. #2
    Join Date
    Oct 2003
    Location
    Denver, Colorado
    Posts
    137

    Re: What is the equivalent of NOT IN using sub select clause in My SQL ?

    For this query, you probably want to do it in two steps. Use this code:
    Code:
    CREATE TEMPORARY TABLE tmp1
    SELECT timeslotstatus.timeslotID FROM timeslotstatus 
    WHERE date='2004-02-19' and equipmentID=1;
    SELECT timeslotID,duration FROM timeslot 
    LEFT JOIN tmp1 ON tmp1.timeslotid = timeslot.timeslotid WHERE timeslot.timeslotID IS NULL;

  3. #3
    Join Date
    Feb 2004
    Posts
    21

    Query not working.

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

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    two steps? no, not necessary

    try this
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Oct 2003
    Location
    Denver, Colorado
    Posts
    137
    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.

    Code:
    select timeslot.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;

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    MySQL gives an error: timeslotID is ambiguous
    good catch!

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

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Oct 2003
    Location
    Denver, Colorado
    Posts
    137
    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.

Posting Permissions

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