I have a database with tables representing data from various years 1997, 1998, 1999, 2000 etc. In the tables I have data representing widget sales, zip codes, types of widgets, for example. Not all widget types were sold in every year, so there are entries in some tables that don’t occur in others.
If I use a left join: SELECT T2000.sales, T1999.sales, T1998.sales, T1997.sales FROM T2000 LEFT JOIN T1999 USING( WidgetType, ZIP ) LEFT JOIN T1998 USING( WidgetType, ZIP ) LEFT JOIN T1997 USING( WidgetType, ZIP ) WHERE T2000.ZIP = 20009 I might get something like... 1250 7800 NULL NULL 7689 2434 8788 NULL 1234 7878 2323 3434 7890 NULL NULL NULL What I want is data from all years where sales occurred. with a left join I get diminishing returns. (if there is a null entry to the left, I never get any output for subsequent tables). What I want is... 1250 7800 NULL 6567 <== last value missing due to left join 7689 2434 8788 NULL NULL 5679 NULL 3434 <== row missing in 'left join' 1234 7878 2323 3434 7890 NULL 5664 NULL <== third value missing because of left join This I believe is a "Full Join". How can I simulate a full join with many tables with the current release of MYSQL ? Can it be done with temorary tables ? Is the solution slow (especially since the left join (with proper indexes) is very snappy)? I saw a reference to "FULL JOINS" being included in 4.1. Is it, in fact, included in the 4.1 alpha release now available? Michael Katzmann --------------------------------------------------------------------- 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
