Sir,
first - don't use varchars to store dates, use one of the date/time datatype instead (date, datetime, timestamp etc), for example:
CREATE TABLE `table1` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`checkin` date DEFAULT NULL,
`checkout` date DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8
CREATE TABLE `table2` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`date` date DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8
second - in your sample data the range is: from '2012-01-01' to '2012-01-15'
and all dates in table2 you inserted
are within this range
- since we are trying to retrieve
dates that are
not in this range, the query returns an empty set.
Please try to insert the following records into table2:
INSERT INTO `test`.`table1` (`checkin`, `checkout`)
VALUES ('2012-01-01', '2012-01-15');
--- these are IN range
INSERT INTO `test`.`table2` (`date`) VALUES ('2012-01-01');
INSERT INTO `test`.`table2` (`date`) VALUES ('2012-01-03');
INSERT INTO `test`.`table2` (`date`) VALUES ('2012-01-05');
INSERT INTO `test`.`table2` (`date`) VALUES ('2012-01-06');
INSERT INTO `test`.`table2` (`date`) VALUES ('2012-01-08');
INSERT INTO `test`.`table2` (`date`) VALUES ('2012-01-10');
-- and these two are NOT in range
INSERT INTO `test`.`table2` (`date`) VALUES ('2011-01-10');
INSERT INTO `test`.`table2` (`date`) VALUES ('2013-01-10');
Query Output:
> select date
from table2
join table1
on table2.date not between table1.checkin and table1.checkout
+ ---------- +
| date |
+ ---------- +
| 2011-01-10 |
| 2013-01-10 |
+ ---------- +
2 rows
Edited 2 time(s). Last edit at 01/28/2012 03:23AM by irek kordirko.