What you want is the FULL OUTER JOIN syntax which MySQL doesn't support. SELECT fruit_table.date, fruit, vegetable FROM fruit_table FULL OUTER JOIN vegetable_table ON (fruit_table.date = vegetable_table.date);
(I'm assuming that NULL is acceptable instead of the empty string for the empty cells in your example.) If you're using MySQL 4.0.x, you can get the same basic result by doing: SELECT fruit_table.date, fruit, vegetable FROM fruit_table LEFT OUTER JOIN vegetable_table ON (fruit_table.date = vegetable_table.date) UNION SELECT fruit_table.date, fruit, vegetable FROM fruit_table RIGHT OUTER JOIN vegetable_table ON (fruit_table.date = vegetable_table.date); If you're using MySQL 3.23.x, you can get the same results by doing two separate queries and simply combining the results in your program: SELECT fruit_table.date, fruit, vegetable FROM fruit_table LEFT OUTER JOIN vegetable_table ON (fruit_table.date = vegetable_table.date); SELECT fruit_table.date, fruit, vegetable FROM fruit_table RIGHT OUTER JOIN vegetable_table ON (fruit_table.date = vegetable_table.date) WHERE fruit_table.date IS NULL; (The WHERE clause there will prevent the duplicates from showing up...) If for some reason you can't deal with two separate result sets and need *one* result set: CREATE TEMPORARY TABLE fv (date DATE NOT NULL, fruit VARCHAR(255), vegetable VARCHAR(255)); INSERT INTO fv SELECT fruit_table.date, fruit, vegetable FROM fruit_table LEFT OUTER JOIN vegetable_table ON (fruit_table.date = vegetable_table.date); INSERT INTO fv SELECT fruit_table.date, fruit, vegetable FROM fruit_table RIGHT OUTER JOIN vegetable_table ON (fruit_table.date = vegetable_table.date) WHERE fruit_table.date IS NULL; SELECT * FROM fv; Note however that if you're using InnoDB tables, both fruit_table and vegetable_table will be locked at the *table level* during the INSERT ... SELECT ... queries! This is not what one might expect to happen, but Heikki assures me it's neccesary to ensure proper transaction isolation semantics. -JF -----Original Message----- From: Jerry Barnes [mailto:[EMAIL PROTECTED]] Sent: Thursday, December 26, 2002 6:32 PM To: [EMAIL PROTECTED] Subject: Riddle Me This... Given the two tables: "fruit_table", and "vegetable_table", how do I get the "Desired ResultSet" below? Thanks... fruit_table +---------------------+--------+ | date | fruit | +---------------------+--------+ | 2002-12-16 00:00:00 | Apple | | 2002-12-17 00:00:00 | Banana | +---------------------+--------+ vegetable_table +---------------------+-----------+ | date | vegetable | +---------------------+-----------+ | 2002-12-17 00:00:00 | Carrot | | 2002-12-18 00:00:00 | Potato | +---------------------+-----------+ Desired ResultSet +---------------------+--------+-----------+ | date | fruit | vegetable | +---------------------+--------+-----------+ | 2002-12-16 00:00:00 | Apple | | | 2002-12-17 00:00:00 | Banana | Carrot | | 2002-12-18 00:00:00 | | Potato | +---------------------+--------+-----------+ SpamFilterBuster: sql --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
