Received: from rm-rstar.sfu.ca (root@rm-rstar.sfu.ca [142.58.120.21]) by hub.org (8.10.1/8.10.1) with ESMTP id e66L66W97087 for ; Thu, 6 Jul 2000 17:06:06 -0400 (EDT) Received: from sfu.ca (jung.psyc.sfu.ca [142.58.172.8]) by rm-rstar.sfu.ca (8.10.1/8.10.1/SFU-5.0H) with ESMTP id e66L5u309770 for ; Thu, 6 Jul 2000 14:05:56 -0700 (PDT) Message-ID: <3964F4B4.89221945@sfu.ca> Date: Thu, 06 Jul 2000 14:05:56 -0700 From: Richard Reply-To: blackw@sfu.ca X-Mailer: Mozilla 4.73 [en] (WinNT; U) X-Accept-Language: en MIME-Version: 1.0 CC: pgsql-sql@postgresql.org Subject: Re: confused by select. References: Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit X-Archive-Number: 200007/65 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