Re: confused by select. - Mailing list pgsql-sql

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