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

[rudy]

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

   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


rudy