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
and sku 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
