[thelist] Re: MySQL two tables SELECT rows that aren't common to both tables

[Simon Perry]

Matt Williams wrote:

>Can you give us some sql to populate the tables with data?
>
>As Brian said above it's very hard to find a solution when you have no
>data to work with.
>
>Matt
>
>  
>
Thanks for all the suggestions so far. Here is the SQL and data for the 
two tables in question.

TIA

Simon

CREATE TABLE `RATES` (
  `RATES_ID` int(11) NOT NULL auto_increment,
  `PROPERTY_ID` int(11) NOT NULL default '0',
  `RATE` double(5,2) NOT NULL default '0.00',
  `START_DATE` date NOT NULL default '0000-00-00',
  `END_DATE` date NOT NULL default '0000-00-00',
  `SPECIAL` enum('No','Yes') NOT NULL default 'No',
  PRIMARY KEY  (`RATES_ID`)
) TYPE=MyISAM AUTO_INCREMENT=20 ;

-- 
-- Dumping data for table `RATES`
-- 

INSERT INTO `RATES` VALUES (1, 1, 795.00, '2005-10-01', '2005-10-22', 'No');
INSERT INTO `RATES` VALUES (2, 1, 1225.00, '2005-08-27', '2005-09-24', 
'No');
INSERT INTO `RATES` VALUES (3, 1, 1825.00, '2005-07-23', '2005-08-10', 
'No');
INSERT INTO `RATES` VALUES (4, 1, 1695.00, '2005-07-16', '2005-07-16', 
'No');
INSERT INTO `RATES` VALUES (5, 2, 645.00, '2005-04-02', '2005-04-30', 'No');
INSERT INTO `RATES` VALUES (6, 2, 895.00, '2005-05-07', '2005-05-21', 'No');
INSERT INTO `RATES` VALUES (7, 2, 1255.00, '2005-05-28', '2005-07-02', 
'No');
INSERT INTO `RATES` VALUES (8, 2, 1395.00, '2005-07-09', '2005-07-09', 
'No');
INSERT INTO `RATES` VALUES (9, 2, 1595.00, '2005-07-16', '2005-07-16', 
'No');
INSERT INTO `RATES` VALUES (10, 2, 1745.00, '2005-07-23', '2005-08-20', 
'No');
INSERT INTO `RATES` VALUES (11, 2, 995.00, '2005-08-27', '2005-09-10', 
'No');
INSERT INTO `RATES` VALUES (12, 2, 945.00, '2005-09-17', '2005-09-24', 
'No');
INSERT INTO `RATES` VALUES (13, 2, 645.00, '2005-10-01', '2005-10-22', 
'No');
INSERT INTO `RATES` VALUES (14, 3, 550.00, '2005-05-01', '2005-05-27', 
'No');
INSERT INTO `RATES` VALUES (15, 3, 875.00, '2005-05-28', '2005-06-30', 
'No');
INSERT INTO `RATES` VALUES (16, 3, 1240.00, '2005-07-01', '2005-07-22', 
'No');
INSERT INTO `RATES` VALUES (17, 3, 1390.00, '2005-07-23', '2005-08-31', 
'No');
INSERT INTO `RATES` VALUES (18, 3, 915.00, '2005-09-01', '2005-09-30', 
'No');
INSERT INTO `RATES` VALUES (19, 4, 725.00, '2005-09-24', '2005-09-24', 
'No');
INSERT INTO `RATES` VALUES (20, 4, 1095.00, '2005-07-02', '2005-07-02', 
'No');

CREATE TABLE `FULL_DATES` (
  `FULL_DATES_ID` int(11) NOT NULL auto_increment,
  `PROPERTY_ID` int(11) NOT NULL default '0',
  `START_DATE` date NOT NULL default '0000-00-00',
  `END_DATE` date NOT NULL default '0000-00-00',
  PRIMARY KEY  (`FULL_DATES_ID`)
) TYPE=MyISAM AUTO_INCREMENT=11 ;

-- 
-- Dumping data for table `FULL_DATES`
-- 

INSERT INTO `FULL_DATES` VALUES (1, 1, '2005-04-16', '2005-11-12');
INSERT INTO `FULL_DATES` VALUES (2, 2, '2005-05-07', '2005-06-11');
INSERT INTO `FULL_DATES` VALUES (3, 2, '2005-06-25', '2005-08-13');
INSERT INTO `FULL_DATES` VALUES (4, 2, '2005-08-27', '2005-09-17');
INSERT INTO `FULL_DATES` VALUES (5, 2, '2005-10-22', '2006-03-04');
INSERT INTO `FULL_DATES` VALUES (6, 4, '2005-05-03', '2005-07-01');
INSERT INTO `FULL_DATES` VALUES (7, 4, '2005-07-09', '2005-09-17');
INSERT INTO `FULL_DATES` VALUES (8, 16, '2005-10-27', '2005-10-27');
INSERT INTO `FULL_DATES` VALUES (9, 15, '2005-06-09', '2005-06-09');
INSERT INTO `FULL_DATES` VALUES (10, 1, '2005-11-26', '2006-01-28');