pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedconfused by select.
7+ messages / 6 participants
[nested] [flat]
* confused by select.
@ 2000-07-06 20:17 John <john@akadine.com>
2000-07-06 20:38 ` Re: confused by select. Brett W. McCoy <bmccoy@chapelperilous.net>
2000-07-06 21:05 ` Re: confused by select. Richard <blackw@sfu.ca>
2000-07-07 09:44 ` Re: confused by select. Patrick Jacquot <patrick.jacquot@anpe.fr>
0 siblings, 3 replies; 7+ messages in thread
From: John @ 2000-07-06 20:17 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: confused by select.
2000-07-06 20:17 confused by select. John <john@akadine.com>
@ 2000-07-06 20:38 ` Brett W. McCoy <bmccoy@chapelperilous.net>
2000-07-06 21:31 ` Re: confused by select. Jan Wieck <JanWieck@t-online.de>
2 siblings, 1 reply; 7+ messages in thread
From: Brett W. McCoy @ 2000-07-06 20:38 UTC (permalink / raw)
To: John <john@akadine.com>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: confused by select.
2000-07-06 20:17 confused by select. John <john@akadine.com>
2000-07-06 20:38 ` Re: confused by select. Brett W. McCoy <bmccoy@chapelperilous.net>
@ 2000-07-06 21:31 ` Jan Wieck <JanWieck@t-online.de>
2000-07-07 01:12 ` Re: confused by select. Brett W. McCoy <bmccoy@chapelperilous.net>
0 siblings, 1 reply; 7+ messages in thread
From: JanWieck@t-online.de @ 2000-07-06 21:31 UTC (permalink / raw)
To: Brett W. McCoy <bmccoy@chapelperilous.net>; +Cc: John <john@akadine.com>; pgsql-sql
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 #
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: confused by select.
2000-07-06 20:17 confused by select. John <john@akadine.com>
2000-07-06 20:38 ` Re: confused by select. Brett W. McCoy <bmccoy@chapelperilous.net>
2000-07-06 21:31 ` Re: confused by select. Jan Wieck <JanWieck@t-online.de>
@ 2000-07-07 01:12 ` Brett W. McCoy <bmccoy@chapelperilous.net>
0 siblings, 0 replies; 7+ messages in thread
From: Brett W. McCoy @ 2000-07-07 01:12 UTC (permalink / raw)
To: Jan Wieck <JanWieck@Yahoo.com>; +Cc: John <john@akadine.com>; pgsql-sql
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"
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: confused by select.
2000-07-06 20:17 confused by select. John <john@akadine.com>
@ 2000-07-06 21:05 ` Richard <blackw@sfu.ca>
2 siblings, 0 replies; 7+ messages in thread
From: Richard @ 2000-07-06 21:05 UTC (permalink / raw)
To: ; +Cc: 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.
>
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
and sku 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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: confused by select.
2000-07-06 20:17 confused by select. John <john@akadine.com>
@ 2000-07-07 09:44 ` Patrick Jacquot <patrick.jacquot@anpe.fr>
2000-07-07 19:32 ` Re: confused by select. Antoine Reid <fluke@kernel-panic.net>
2 siblings, 1 reply; 7+ messages in thread
From: Patrick Jacquot @ 2000-07-07 09:44 UTC (permalink / raw)
To: John <john@akadine.com>; +Cc: 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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: confused by select.
2000-07-06 20:17 confused by select. John <john@akadine.com>
2000-07-07 09:44 ` Re: confused by select. Patrick Jacquot <patrick.jacquot@anpe.fr>
@ 2000-07-07 19:32 ` Antoine Reid <fluke@kernel-panic.net>
0 siblings, 0 replies; 7+ messages in thread
From: Antoine Reid @ 2000-07-07 19:32 UTC (permalink / raw)
To: Patrick Jacquot <patrick.jacquot@anpe.fr>; +Cc: John <john@akadine.com>; pgsql-sql
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
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2000-07-07 19:32 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2000-07-06 20:17 confused by select. John <john@akadine.com>
2000-07-06 20:38 ` Brett W. McCoy <bmccoy@chapelperilous.net>
2000-07-06 21:31 ` Jan Wieck <JanWieck@t-online.de>
2000-07-07 01:12 ` Brett W. McCoy <bmccoy@chapelperilous.net>
2000-07-06 21:05 ` Richard <blackw@sfu.ca>
2000-07-07 09:44 ` Patrick Jacquot <patrick.jacquot@anpe.fr>
2000-07-07 19:32 ` Antoine Reid <fluke@kernel-panic.net>
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