Re: confused by select.

John wrote:
> 
> Hello.  I'm trying to do a select here that i have looked at from many
> angles and cannot find a solution too.  My main problem, (i believe) is
> that it is trying to create a many to many relationship.  I would be
> grateful if anyone knew a way around this.
> 

I believe that you would find your task to be MUCH simpler if you
normalize your HISTORY (t2) table.  That is, don't lump all the SKUs
from one purchase in a single record.

You could normalize this table a bit:
    create history (id char(6), purchase_order char(6), sku char(4));
e.g.:     id     purchase_order    sku               ----------------------------------                4001
A55321        1111                4001       A55321         1212                4001       A55321         W233
     (...)
 

     select distinct id form history where sku in (select sku from t1
where type='W');

Or you could even normalize it further:

    create sale (id char(6), purchase_order char(6));    create history (purchase_order char(6), sku char(4));
     select distinct id          from sales s, history h          where s.purchase_order = h.purchase_order
andsku in (select sku from t1 where type='W');
 

You will find at least the following two things are true if you
normalize this data:

1. The query you are trying to pose will become simple.
2. Queries involving the SKU number in your HISTORY table will be solved
for much quicker.


Cheers,
Richard