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

rudy r937 at interlog.com
Fri Dec 6 07:35:01 CST 2002

>> 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?

no, a second data column

based on your explanation of the countries involved, perhaps it is better to
change your shipping_zone table, rather than implement a zone-country

   item (item_id, name, description, cost)

   zone (zone_id, name)

   item_zone (item_id, zone_id, shippingcost, overrideprice)

since you were dealing with only 3 shipping zones (US, Canada, and
Hawaii/Alaska) and only 2 pricing countries (US vs. Canada), just keep three
entries in the zone table and add an override price, which will be null if
there is no difference from the base cost in the item record

use paul's idea of coalescing the override prices (nice one, paul)

then your existing query needs only a slight modification, eliminating the
extra lookup, and you're good to go


More information about the thelist mailing list