[thelist] SQL: Need help for query within a store

Joshua Olson joshua at waetech.com
Fri Dec 6 07:21:01 CST 2002


----- Original Message -----
From: "rudy" <r937 at interlog.com>
Sent: Thursday, December 05, 2002 10:50 PM


> > But, now I must add in the idea that each product may have a
> > different base cost depending on the country the item is to be
> > shipped to (not shipping code, but actual item cost)
>
> if the different base cost varies by country (as you say), then you have
to
> have a country table and a separate many-to-many price-country
relationship
> table

Yes, that was my thought.  Since the system is only talking about a couple
of countries in this case (US vs. Canada) I was going to create a "pricing
zone" table, but it is essentially the same thing as a country table with
two records.

> if, however, base cost varies by zone, then simply add another field to
your
> item_shipping_zone_assoc relationship table

So do you mean have a 3-way PK on this table?  item, shipping zone, pricing
zone?

> if you have the former case, then bringing together two disjoint sets of
> data and presenting them in a "neat and orderly" fashion, two adjacent but
> unrelated columns of data, then that's a mess and i despair of doing it
with
> queries

That's unfortunately what I thought to.  Since my original single query with
the two inner joins correctly grabbed the shipping zone information and I
already had output routines for the data (and my client wants this pricing
display done right now), I had to do what I didn't want to do.  Each row now
does a separate lookup for pricing zones for the item of that row.  If there
are no pricing records found for the item, it displays the default price for
the item from the item table.  If pricing records are found, it loops
through them and displays them in the pricing column of the display output.
Ug.

> unless, of course, the country table is made a child table of the zone
> table....

Do tell!  What do you mean?

> if, however, you can add a base cost column to your
item_shipping_zone_assoc
> table, then it's trivial, right?

I'm not sure I follow.  Currently the base price is in the item table, just
in case there are no shipping zones created for a particular item.  What
technique qould benefit from moving the base price to the
shipping_zone_assoc table?

> > Currently, I use two inner joins...
>
> within a single query, right?

Of course.

-joshua




More information about the thelist mailing list