Hi,
On Wed, 2003-02-19 at 15:56, Michael Katzmann wrote:
> 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
I think the query you are doing doesn't reflect your purpose.
I'm not shure that what i'm going to say is right, but have a thought
about this.
You're left joining T2000 with T1999, and T1999 with 1998, and T1998
with 1997. When a record exists in T2000 but does not exist in T1999,
the T1999.sales values (for that record) will be null and then the join
between and T1999 with T1998 (for that record) will only return NULL
values and the same for subsequent tables...
That's why:
> if there is
> a null entry to the left, I never get any output for
> subsequent tables
Maybe what you want would be something like:
SELECT T2000.sales, T1999.sales, T1998.sales, T1997.sales
FROM T2000
LEFT JOIN T1999
USING( WidgetType, ZIP )
LEFT JOIN T1998
ON (T2000.WidgetType=T1998.WidgetType AND T2000.ZIP=T1998.ZIP)
LEFT JOIN T1997
ON (T2000.WidgetType=T1997.WidgetType AND T2000.ZIP=T1997.ZIP)
WHERE T2000.ZIP = 20009
This query also is not 100% right because if there isn't a WidgetType in
T2000 with ZIP=20009 that could exist, for example, in T1999, then that
WidgetType would not appear at all.
If this is a problem (there aren't all possible WidgetTypes for
ZIP=20009 in T2000), maybe you should first get all possible WidgetType
with ZIP=20009 to a temporary table and then substitute in the query
above the table T2000 for that temporary table and add T2000 like the
other years...
> 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?
I'm not shure to answer about this so i'll leave it for the MySQL
"gurus" or others that can help..
> Michael Katzmann
>
--
Diana Soares
---------------------------------------------------------------------
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
