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