[thelist] Re: MySQL two tables SELECT rows that aren't common to both tables
Simon Perry
thelist at si-designs.co.uk
Thu Jun 16 05:58:16 CDT 2005
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');
More information about the thelist
mailing list