Received: from mickey.anpe.fr (smtp1.anpe.fr [195.101.137.20]) by hub.org (8.10.1/8.10.1) with ESMTP id e677nPW34957 for ; Fri, 7 Jul 2000 03:49:25 -0400 (EDT) Received: from antivirus.anpe.fr ([10.0.4.95]) by mickey.anpe.fr (Netscape Messaging Server 4.05) with SMTP id FXBHPQ00.7AY; Fri, 7 Jul 2000 09:49:02 +0200 Message-ID: <3965A67C.9D1E6869@anpe.fr> Date: Fri, 07 Jul 2000 11:44:30 +0200 From: Patrick Jacquot Organization: cti Noisiel X-Mailer: Mozilla 4.7 [en] (X11; I; Linux 2.2.9-19mdk i586) X-Accept-Language: en MIME-Version: 1.0 To: John 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/73 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. > > Here's my predicamint. > I have a database for sales orders. > An inventory table. > And > A history table. > > Inventory: > Create t1 (sku char(4), type char(1)); > History: > Create t2 (id char(6), items text); > > [There are more fields, but this is all that matters for this query] > > I would like to get the id's where the customer has purchased an item of a > specific type. > > Problem A: most people order more than one item at a time. > So the 'items' field is a colon delimitted text field containing the > skus of the purchased items. > > Problem B: there are many skus of each type. > as are there many purchases. > > What would the proper select be? > > create view v1 (select sku from t1 where type ='K'); > will get me all the skus of one type but i don't know where to go > from there. And it feels as if i've exhausted all options. > > i've been working around: > select id from t2 where items like sku; > and no matter what i use in the where clause (regex, like, or wildcards). > i get back an error or a zero. > and there are no other related fields in the mentioned tables. > > is there a way to step through the sku field item by item without leaving > postgres (i.e. resorting to scripting)? > > I have also tried different fieldtypes for the 'items' field. > But they all give me problems too. > The array works much the same way as the : delimitted field i have does. > Except you have less operators that work with it. > And to break it up into separate items fields. (item1, item2, item3, > etc.) is a waste, seeing as the average order is 2.? but there are many > orders with hundreds of items. > > Sorry for the long winded explanation. > But I figured, that the more imformation i gave, the more someone may be > able to help. > > Thanks in advance. > .jtp usually many-to-many relationships are handled by a third table, like this: create table items (item_id,...) create table customers (customer_id, ...) create table orders (customer_id, item_id, quantity_orderered) Hoping it may help Patrick JACQUOT