pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
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



view thread (7+ messages)  latest in thread

Message-ID: <3964F4B4.89221945@sfu.ca>
Permalink:  ../3964F4B4.89221945@sfu.ca/
Also on:    postgresql.org/message-id/3964F4B4.89221945@sfu.ca

 · 

reply

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