#1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2012
    Posts
    33
    Rep Power
    2

    Mysql-query-notin


    hello! I have this tables in my database, hours containing (8:00,8:30,9:00, 9:30, 10:00, 10:30, 11:00, 11:30)
    and schedules table having columns starttime and endtime.
    starttime has 8:00 and endtime has 9:00

    how can I remove 8:30 and 9:00 from hours
    I use this not in. But only removes 8:00
    PHP Code:
    select hourtitle from hours where (hourtitlenot in (select starttime from schedules
    Any idea??
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,280
    Rep Power
    4279
    in order for us to help you with this type of problem (where the sql you posted looks okay but the results are not what you expect) you will need to provide two things:

    1. CREATE TABLE statements for the tables
    2. sample data in the form of INSERT statements

    both of these can be easily provided by dumping the tables
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2012
    Posts
    33
    Rep Power
    2
    Thanks for the reply.

    This is the create for schedules
    PHP Code:
    'schedules''CREATE TABLE `schedules` (
      `schedid` int(3) NOT NULL AUTO_INCREMENT,
      `recordid` int(3) NOT NULL,
      `patname` varchar(45) NOT NULL,
      `schedtype` varchar(45) NOT NULL,
      `scheddate` date NOT NULL,
      `room` varchar(45) NOT NULL,
      `starttime` time NOT NULL,
      `endtime` time NOT NULL,
      `status` varchar(45) NOT NULL,
      `therapist` varchar(45) NOT NULL,
      PRIMARY KEY (`schedid`),
      KEY `recordid` (`recordid`)
    ) ENGINE=InnoDB AUTO_INCREMENT=95 DEFAULT CHARSET=latin1' 
    This is the create for hours
    PHP Code:
    'hours''CREATE TABLE `hours` (
      `hourid` int(3) NOT NULL AUTO_INCREMENT,
      `hourtitle` time NOT NULL,
      `status` varchar(45) NOT NULL,
      PRIMARY KEY (`hourid`)
    ) ENGINE=InnoDB AUTO_INCREMENT=17 DEFAULT CHARSET=latin1' 
    This is the insert for schedules
    PHP Code:
    INSERT INTO `schedules`(`schedid`, `recordid`, `patname`, `schedtype`, `scheddate`, `room`, `starttime`, `endtime`, `status`, `therapist`) VALUES ('1','1''Sample Patientname','Intake Interview','2013-01-03','Room 1','10:00:00','11;00:00','Ongoing','Therapist Name'
    This is the insert for hours
    PHP Code:
    INSERT INTO `hours`(`hourid`, `hourtitle`, `status`) VALUES ('1','10:00:00''Available'

    =(
  6. #4
  7. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,280
    Rep Power
    4279
    thanks

    i tried your query on your data and it works great

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  8. #5
  9. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2012
    Posts
    33
    Rep Power
    2
    Thanks! so what is the problem sir? =)
  10. #6
  11. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,280
    Rep Power
    4279
    Originally Posted by bads
    Thanks! so what is the problem sir? =)
    hey, it's your data, and your requirement, so if there's a problem, you gotta tell me
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  12. #7
  13. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2012
    Posts
    33
    Rep Power
    2
    Originally Posted by r937
    hey, it's your data, and your requirement, so if there's a problem, you gotta tell me

    What about my data sir? I dont encounter something wrong with my data? Maybe its the query?
  14. #8
  15. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,280
    Rep Power
    4279
    Originally Posted by bads
    What about my data sir? I dont encounter something wrong with my data?
    the data you gave me had only one row in each table, so obviously, i couldn't find the problem that's in your actual data

    in your actual data, there's something wrong which i can't see

    if you want me to keep looking into this, you'll have to try to re-create the scenario that you're having
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  16. #9
  17. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2012
    Posts
    95
    Rep Power
    3
    Originally Posted by bads
    hello! I have this tables in my database, hours containing (8:00,8:30,9:00, 9:30, 10:00, 10:30, 11:00, 11:30)
    and schedules table having columns starttime and endtime.
    starttime has 8:00 and endtime has 9:00

    how can I remove 8:30 and 9:00 from hours
    I use this not in. But only removes 8:00
    select hourtitle from hours where (hourtitle) not in (select starttime from schedules)

    Any idea??
    Hi,

    I'm a bit unclear on the question. if you are nesting the query "select starttime from schedules" and the startime only contains 8:00 , then of course it will only remove 8:00 !
    you could remove 9:00 using "not in (select endtime from schedules)" but how would you expect this query to remove 8:30 ? where would it find that value in order to know it needs to be removed?

    as a guess, what you are looking for is something more like this:
    select hourtitle from hours where (hourtitle) not between (select starttime from schedules) and (select endtime from schedules)
  18. #10
  19. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2012
    Posts
    33
    Rep Power
    2
    Originally Posted by bobert123
    Hi,

    I'm a bit unclear on the question. if you are nesting the query "select starttime from schedules" and the startime only contains 8:00 , then of course it will only remove 8:00 !
    you could remove 9:00 using "not in (select endtime from schedules)" but how would you expect this query to remove 8:30 ? where would it find that value in order to know it needs to be removed?

    as a guess, what you are looking for is something more like this:
    Hello thanks for the reply! FYI it is a scheduling program. First, the time schedules are based on HOURS table which has 8:00, 8:30, 9:00, 9:30 and so on. 30 mins interval.

    Now, if the the time slot 8:00-9:00 is taken, I want to remove 8:00, 8:30, 9:00 from the choices which is HOURS table because obviously it is taken. The second table is SCHEDULES table that has columns of STARTTIME and ENDTIME. Btw, the query that you've given me says "Subquery returns more than 1 row". I hope I explained it well. =)

IMN logo majestic logo threadwatch logo seochat tools logo