SGreen@stripped wrote:
>
> I would simplify it by converting everything to us explicit (not
> implicit) JOIN statements,skipping unnecessary type conversions, and
> logically merging your conditions. Here is your original query,
> slightly reformatted.
>
> SELECT DISTINCT i.basename
> FROM inventory i, transaction t, customer c
> WHERE i.prodcode = t.prodcode
> and c.custcode = t.custcode
> and i.basename is not NULL
> and i.prodname is not NULL
> and (
> (date(t.date) >= "2004-01-01"
> and date(t.date) <= "2004-01-31"
> )
> and i.basename IN (
> select DISTINCT ii.basename
> from inventory ii, transaction tt, customer cc
> where ii.prodcode = tt.prodcode
> and cc.custcode = tt.custcode
> and ii.basename is not NULL
> and ii.prodname is not NULL
> and(
> date(tt.date) >= "2005-01-01"
> and date(tt.date) <= "2005-01-31")
> )
> )
> order by i.basename
>
> Here is my proposal:
>
> 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 Shawn, this works great. This will give all products that exist in
two different period. What if I also want to list products that in
period 1 but not in peirod 2; while other products in preriod 2 but not
in period 1? Do you think simply take out the join for i.prodcode =
t.prodcode and i.prodcode = tt.prodcode and use left/right join will
yield the desired result?
Thanks
Sam
> Shawn Green
> Database Administrator
> Unimin Corporation - Spruce Pine
