--source include/have_innodb.inc

DROP TABLE IF EXISTS `B`;
CREATE TABLE `B` (
  `pk` int(11) NOT NULL AUTO_INCREMENT,
  `int_nokey` int(11) NOT NULL,
  `int_key` int(11) NOT NULL,
  `date_key` date NOT NULL,
  `date_nokey` date NOT NULL,
  `time_key` time NOT NULL,
  `time_nokey` time NOT NULL,
  `datetime_key` datetime NOT NULL,
  `datetime_nokey` datetime NOT NULL,
  `varchar_key` varchar(1) DEFAULT NULL,
  `varchar_nokey` varchar(1) DEFAULT NULL,
  PRIMARY KEY (`pk`),
  KEY `int_key` (`int_key`),
  KEY `date_key` (`date_key`),
  KEY `time_key` (`time_key`),
  KEY `datetime_key` (`datetime_key`),
  KEY `varchar_key` (`varchar_key`)
) ENGINE=InnoDB;

INSERT INTO `B` VALUES (1,5,5,'0000-00-00','0000-00-00','00:00:00','00:00:00','2009-04-12 00:00:00','2009-04-12 00:00:00','o','o');

CREATE TABLE `BB` (
  `pk` int(11) NOT NULL AUTO_INCREMENT,
  `int_nokey` int(11) NOT NULL,
  `int_key` int(11) NOT NULL,
  `date_key` date NOT NULL,
  `date_nokey` date NOT NULL,
  `time_key` time NOT NULL,
  `time_nokey` time NOT NULL,
  `datetime_key` datetime NOT NULL,
  `datetime_nokey` datetime NOT NULL,
  `varchar_key` varchar(1) DEFAULT NULL,
  `varchar_nokey` varchar(1) DEFAULT NULL,
  PRIMARY KEY (`pk`),
  KEY `int_key` (`int_key`),
  KEY `date_key` (`date_key`),
  KEY `time_key` (`time_key`),
  KEY `datetime_key` (`datetime_key`),
  KEY `varchar_key` (`varchar_key`)
) ENGINE=InnoDB;

INSERT INTO `BB` VALUES (1,6,6,'0000-00-00','0000-00-00','11:01:28','11:01:28','2007-08-09 06:23:37','2007-08-09 06:23:37','c','c');

CREATE TABLE `C` (
  `pk` int(11) NOT NULL AUTO_INCREMENT,
  `int_nokey` int(11) NOT NULL,
  `int_key` int(11) NOT NULL,
  `date_key` date NOT NULL,
  `date_nokey` date NOT NULL,
  `time_key` time NOT NULL,
  `time_nokey` time NOT NULL,
  `datetime_key` datetime NOT NULL,
  `datetime_nokey` datetime NOT NULL,
  `varchar_key` varchar(1) DEFAULT NULL,
  `varchar_nokey` varchar(1) DEFAULT NULL,
  PRIMARY KEY (`pk`),
  KEY `int_key` (`int_key`),
  KEY `date_key` (`date_key`),
  KEY `time_key` (`time_key`),
  KEY `datetime_key` (`datetime_key`),
  KEY `varchar_key` (`varchar_key`)
) ENGINE=InnoDB;

INSERT INTO `C` VALUES (1,5,5,'2003-12-26','2003-12-26','08:05:38','08:05:38','0000-00-00 00:00:00','0000-00-00 00:00:00',NULL,NULL),(2,5,5,'0000-00-00','0000-00-00','15:34:08','15:34:08','0000-00-00 00:00:00','0000-00-00 00:00:00','p','p'),(3,0,0,'0000-00-00','0000-00-00','00:00:00','00:00:00','2009-09-14 08:36:26','2009-09-14 08:36:26',NULL,NULL),(4,6,6,'2006-03-07','2006-03-07','00:00:00','00:00:00','0000-00-00 00:00:00','0000-00-00 00:00:00','e','e'),(5,0,0,'2006-07-08','2006-07-08','00:00:00','00:00:00','0000-00-00 00:00:00','0000-00-00 00:00:00','w','w'),(6,0,0,'2006-11-21','2006-11-21','23:58:52','23:58:52','2005-12-02 14:27:44','2005-12-02 14:27:44',NULL,NULL),(7,3,3,'0000-00-00','0000-00-00','00:00:00','00:00:00','0000-00-00 00:00:00','0000-00-00 00:00:00','p','p'),(8,9,9,'0000-00-00','0000-00-00','00:00:00','00:00:00','0000-00-00 00:00:00','0000-00-00 00:00:00','d','d'),(9,5,5,'2002-09-23','2002-09-23','00:00:00','00:00:00','0000-00-00 00:00:00','0000-00-00 00:00:00','w','w'),(10,0,0,'0000-00-00','0000-00-00','00:00:00','00:00:00','0000-00-00 00:00:00','0000-00-00 00:00:00','z','z');

CREATE TABLE `CC` (
  `pk` int(11) NOT NULL AUTO_INCREMENT,
  `int_nokey` int(11) NOT NULL,
  `int_key` int(11) NOT NULL,
  `date_key` date NOT NULL,
  `date_nokey` date NOT NULL,
  `time_key` time NOT NULL,
  `time_nokey` time NOT NULL,
  `datetime_key` datetime NOT NULL,
  `datetime_nokey` datetime NOT NULL,
  `varchar_key` varchar(1) DEFAULT NULL,
  `varchar_nokey` varchar(1) DEFAULT NULL,
  PRIMARY KEY (`pk`),
  KEY `int_key` (`int_key`),
  KEY `date_key` (`date_key`),
  KEY `time_key` (`time_key`),
  KEY `datetime_key` (`datetime_key`),
  KEY `varchar_key` (`varchar_key`)
) ENGINE=InnoDB;

INSERT INTO `CC` VALUES (1,3,3,'2005-07-18','2005-07-18','15:58:39','15:58:39','2004-03-27 03:22:05','2004-03-27 03:22:05',NULL,NULL),(2,7,7,'0000-00-00','0000-00-00','00:00:00','00:00:00','2008-02-28 20:02:09','2008-02-28 20:02:09','x','x'),(3,0,0,'2004-07-05','2004-07-05','00:00:00','00:00:00','0000-00-00 00:00:00','0000-00-00 00:00:00','u','u'),(4,9,9,'2007-09-06','2007-09-06','12:29:03','12:29:03','0000-00-00 00:00:00','0000-00-00 00:00:00',NULL,NULL),(5,7,7,'2005-01-24','2005-01-24','12:37:03','12:37:03','2006-07-26 00:00:00','2006-07-26 00:00:00','{','{'),(6,5,5,'2003-03-07','2003-03-07','04:13:56','04:13:56','2007-06-15 23:13:40','2007-06-15 23:13:40','i','i'),(7,0,0,'0000-00-00','0000-00-00','00:00:00','00:00:00','0000-00-00 00:00:00','0000-00-00 00:00:00',NULL,NULL),(8,2,2,'2004-08-28','2004-08-28','00:00:00','00:00:00','0000-00-00 00:00:00','0000-00-00 00:00:00','i','i'),(9,4,4,'0000-00-00','0000-00-00','22:27:41','22:27:41','0000-00-00 00:00:00','0000-00-00 00:00:00','a','a'),(10,2,2,'2001-06-22','2001-06-22','15:27:12','15:27:12','2001-08-25 20:44:07','2001-08-25 20:44:07','t','t');

SELECT  OUTR . `int_key`
FROM B AS OUTR2
LEFT JOIN C AS OUTR
ON ( OUTR2 . `varchar_nokey` > OUTR . `varchar_key` )
WHERE OUTR . `pk` IN (
 SELECT  INNR . `int_key` AS Y
 FROM BB AS INNR2
 LEFT JOIN CC AS INNR
 ON ( INNR2 . `date_nokey` >= INNR . `date_key` )
);