Thread: confused by select.
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
On Thu, 6 Jul 2000, John wrote: > 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> I don't understand why you are doing it this way? Why not create a history table with individual skus that are each part of an order? create table history (id int2, order int2, sku char(4)); You would, of course, put some constraints to make sure that skus in the history table actually exist in the inventory table (i.e., foreign key), and have the history id as a serial type to make the primary key. Then you can have the same order number reference multiple inventory items. Then you can do easier joins, search for unique orders with a count of items in each order, and so forth, all in SQL. Brett W. McCoy http://www.chapelperilous.net --------------------------------------------------------------------------- Virtue does not always demand a heavy sacrifice -- only the willingness to make it when necessary. -- Frederick Dunn
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
andsku 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
Brett W. McCoy wrote: > On Thu, 6 Jul 2000, John wrote: > > > 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> > > I don't understand why you are doing it this way? Why not create a > history table with individual skus that are each part of an order? > > create table history (id int2, order int2, sku char(4)); > > You would, of course, put some constraints to make sure that skus in the > history table actually exist in the inventory table (i.e., foreign key), > and have the history id as a serial type to make the primary key. Then you > can have the same order number reference multiple inventory items. > > Then you can do easier joins, search for unique orders with a count of > items in each order, and so forth, all in SQL. IMHO the correct suggestion. Just want to underline it. A list of purchases is usually a subset of another relation. Remember, RDBMS means RELATIONAL Database Management System! So if you setup your tables with a relational angle of view, the system will do well. Jan -- #======================================================================# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #================================================== JanWieck@Yahoo.com #
On Thu, 6 Jul 2000, Jan Wieck wrote: > IMHO the correct suggestion. Just want to underline it. > > A list of purchases is usually a subset of another relation. > Remember, RDBMS means RELATIONAL Database Management System! > So if you setup your tables with a relational angle of view, > the system will do well. I was going to say that, but assumed it would be obvious. :-) Brett W. McCoy http://www.chapelperilous.net --------------------------------------------------------------------------- Conscience is the inner voice that warns us somebody may be looking. -- H.L. Mencken, "A Mencken Chrestomathy"
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
On Fri, Jul 07, 2000 at 11:44:30AM +0200, Patrick Jacquot wrote: [snip] > > 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) Hi! In real life situations, I can only see one possible problem with that. Let's say you have a product in stock, for a long period of time, and at some point, you need to change the description of the product... Now what happens when you look at your old invoices? They show up with the new description.. What if you need to delete a product? :) What if the shipping address changes? I have found, as a requirement in many of my projects, that we be able to view invoices/orders /exactly/ like they were.. It involves a different set of tables, one for the 'open' orders, inventory, etc and another set of tables, that are populated for every invoice. (Yes, that means lots of duplication..) > > Hoping it may help > > Patrick JACQUOT > just my 1/50$ CDN (for what it's worth nowadays...) Antoine Reid
Hello people, I need answers to a simple question that I couldn't find the definite answer for: Does Postgresql support only EUC? Basically, I am trying to save international fonts to Postgresql, so I'm trying to find out what exactly I need to do. It seems like it works fine when I am using PHP to insert/retrieve data without any encoding conversions. I think my Postgresql was not specified any special encoding at the time of make. Do I need to convert everything EUC before I store data, and do why would I need to do that? Thanks in advance!! Tony
> Does Postgresql support only EUC? Basically, I am trying to save > international > fonts to Postgresql, so I'm trying to find out what exactly I need to do. I'd suggest you try the Unicode UTF-8 encoding. It supports many languages, including Japanese of course, and needs no modification of existing programs, if you don't rely on character counting for output formatting. Volker Paul
Volker, Thank you, I was using UTF8 because I am accessing the database via JDBC, and Java's default encoding is UTF8. I wasn't sure about whether it was a right thing to do or not. Thank you very much. I really appreciate your help! Tony Nakamura ----- Original Message ----- From: Volker Paul <vpaul@dohle.com> To: Tony Nakamura <tony@uickarate.com>; <pgsql-sql@postgresql.org> Sent: Tuesday, July 11, 2000 2:38 AM Subject: Re: [SQL] Supported Encoding > > Does Postgresql support only EUC? Basically, I am trying to save > > international > > fonts to Postgresql, so I'm trying to find out what exactly I need to do. > > I'd suggest you try the Unicode UTF-8 encoding. It supports many > languages, including Japanese of course, and needs no modification > of existing programs, if you don't rely on character counting > for output formatting. > > Volker Paul >