Mysql-query-notin

Dev Shed Forums Sponsor:

February 2nd, 2013, 09:50 AM

bads

Contributing User

Join Date: Dec 2012

Posts: 33

Time spent in forums: 6 h 22 m 35 sec
Reputation Power: 1

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 (hourtitle) not in (select starttime from schedules)

Any idea??

February 2nd, 2013, 11:13 AM

r937

SQL Consultant

Join Date: Feb 2003

Location: Toronto Canada

Posts: 26,371

Time spent in forums: 3 Months 1 Week 2 Days 6 h 32 m 9 sec
Reputation Power: 4140

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

__________________
r937.com | rudy.ca
please visit Simply SQL and buy my book

February 3rd, 2013, 08:36 AM

bads

Contributing User

Join Date: Dec 2012

Posts: 33

Time spent in forums: 6 h 22 m 35 sec
Reputation Power: 1

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')

February 3rd, 2013, 08:57 AM

r937

SQL Consultant

Join Date: Feb 2003

Location: Toronto Canada

Posts: 26,371

Time spent in forums: 3 Months 1 Week 2 Days 6 h 32 m 9 sec
Reputation Power: 4140

thanks

i tried your query on your data and it works great

February 3rd, 2013, 03:42 PM

bads

Contributing User

Join Date: Dec 2012

Posts: 33

Time spent in forums: 6 h 22 m 35 sec
Reputation Power: 1

Thanks! so what is the problem sir? =)

February 3rd, 2013, 05:26 PM

r937

SQL Consultant

Join Date: Feb 2003

Location: Toronto Canada

Posts: 26,371

Time spent in forums: 3 Months 1 Week 2 Days 6 h 32 m 9 sec
Reputation Power: 4140

Quote:

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

February 4th, 2013, 09:29 PM

bads

Contributing User

Join Date: Dec 2012

Posts: 33

Time spent in forums: 6 h 22 m 35 sec
Reputation Power: 1

Quote:

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?

February 5th, 2013, 02:17 AM

r937

SQL Consultant

Join Date: Feb 2003

Location: Toronto Canada

Posts: 26,371

Time spent in forums: 3 Months 1 Week 2 Days 6 h 32 m 9 sec
Reputation Power: 4140

Quote:

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

February 10th, 2013, 03:25 AM

bobert123

Contributing User

Join Date: Mar 2012

Posts: 43

Time spent in forums: 8 h 26 m 15 sec
Reputation Power: 2

Quote:

Originally Posted by bads

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:

Quote:

select hourtitle from hours where (hourtitle) not between (select starttime from schedules) and (select endtime from schedules)

#10

February 11th, 2013, 07:34 AM

bads

Contributing User

Join Date: Dec 2012

Posts: 33

Time spent in forums: 6 h 22 m 35 sec
Reputation Power: 1

Quote:

Originally Posted by bobert123

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. =)

Mysql-query-notin

Developer Shed Advertisers and Affiliates