Re: confused by select.

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