| |
|
If this is your first visit, be sure to check out the FAQ by clicking the link above.
You may have to register before you can post: click the register link above to proceed.
To start viewing messages, select the forum that you want to visit from the selection below.
|
 |
02-20-04, 02:30
|
|
Registered User
|
|
Join Date: Feb 2004
Posts: 21
|
|
|
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;
|
|
02-20-04, 03:22
|
|
Registered User
|
|
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.
|
|
02-20-04, 04:13
|
|
Registered User
|
|
Join Date: Oct 2003
Location: Slovakia
Posts: 482
|
|
|
Re: NOT IN clause in My SQL not working
|
|
Quote:
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...
|
|
02-21-04, 04:31
|
|
Registered User
|
|
Join Date: Feb 2004
Posts: 21
|
|
Quote:
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.
|
|
|
02-21-04, 07:32
|
|
SQL Consultant
|
|
Join Date: Apr 2002
Location: Toronto, Canada
Posts: 19,534
|
|
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
|
|
02-25-04, 04:46
|
|
Registered User
|
|
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.
|
|
02-25-04, 11:07
|
|
Registered User
|
|
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.
|
|
02-26-04, 03:20
|
|
Registered User
|
|
Join Date: Feb 2004
Posts: 21
|
|
Quote:
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 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.
|
|
|
| Thread Tools |
Search this Thread |
|
|
|
| Display Modes |
 Linear Mode
|
Posting Rules
|
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
HTML code is Off
|
|
|
|
|
dBforums
>
NOT IN clause in My SQL not working
