SGreen@stripped wrote:
>
>
> SELECT DISTINCT i.basename
> FROM inventory i
> INNER JOIN transaction t
> ON i.prodcode = t.prodcode
> AND t.date >= '2004-01-01'
> AND t.date <= '2004-01-31'
> INNER JOIN transaction tt
> ON i.prodcode = tt.prodcode
> AND tt.date >= '2005-01-01'
> AND tt.date <= '2005-01-31'
> INNER JOIN customer c
> ON c.custcode = t.custcode
> AND c.custcode = tt.custcode
> WHERE i.basename is not NULL
> and i.prodname is not NULL
> order by i.basename
>
> This should give you a list of inventory basenames for all current
> customers (their names are still in the customer table) that "had
> transactions" (ordered products?) during both JAN 2004 and JAN 2005.
> This list will show only the products that were ordered during BOTH
> time periods BY THE SAME CUSTOMER at least once (at least one repeat
> sale, year to year, in JAN). Is this what you were after or was there
> a different question you were trying to answer?
>
Hi, while this is much faster than subquery, I found there is "cumsy"
way faster than this series inner joins.
I dicovered that if I create two different temporary tables with "create
view as select ..." for 2 differnet period, the join between this temp
tables is also much faster than this series inner joins approach. With
160000 records in the transaction table, the series inner joins takes
very long time (more than 15 minutes) give out a result.
Any comment?
Thanks
Sam
> Shawn Green
> Database Administrator
> Unimin Corporation - Spruce Pine
