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
containingthe 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
togo
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
