From: | Richard <blackw(at)sfu(dot)ca> |
---|---|
To: | |
Cc: | pgsql-sql(at)postgresql(dot)org |
Subject: | Re: confused by select. |
Date: | 2000-07-06 21:05:56 |
Message-ID: | 3964F4B4.89221945@sfu.ca |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Jan Wieck | 2000-07-06 21:09:27 | Re: [SQL] Re: lztext and compression ratios... |
Previous Message | Ebbe Poulsen | 2000-07-06 20:59:07 | Get: Month & Month-1 & Month+1 |