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
