From: Richard <blackw@sfu.ca>
Cc: pgsql-sql@postgresql.org
Subject: Re: confused by select.
Date: Thu, 06 Jul 2000 14:05:56 -0700
Message-ID: <3964F4B4.89221945@sfu.ca> (raw)
References: <Pine.BSF.4.21.0007061558550.13354-100000@db.akadine.com>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-sql@postgresql.org
Cc: blackw@sfu.ca
Subject: Re: confused by select.
In-Reply-To: <3964F4B4.89221945@sfu.ca>
References: <Pine.BSF.4.21.0007061558550.13354-100000@db.akadine.com> <3964F4B4.89221945@sfu.ca>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox