pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Patrick Jacquot <patrick.jacquot@anpe.fr>
To: John <john@akadine.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: confused by select.
Date: Fri, 07 Jul 2000 11:44:30 +0200
Message-ID: <3965A67C.9D1E6869@anpe.fr> (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.
>
> 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.
>       <example of items field -- 1111:1212:W233:QA66>
> 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




view thread (7+ messages)  latest in thread

Message-ID: <3965A67C.9D1E6869@anpe.fr>
Permalink:  ../3965A67C.9D1E6869@anpe.fr/
Also on:    postgresql.org/message-id/3965A67C.9D1E6869@anpe.fr

 · 

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: patrick.jacquot@anpe.fr, john@akadine.com
  Subject: Re: confused by select.
  In-Reply-To: <3965A67C.9D1E6869@anpe.fr>
  References: <Pine.BSF.4.21.0007061558550.13354-100000@db.akadine.com> <3965A67C.9D1E6869@anpe.fr>

* 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