[thelist] SQL: Need help for query within a store
Paul Cowan
evolt at funkwit.com
Thu Dec 5 21:44:01 CST 2002
Joshua wrote:
> Is this reasonably possible to do with 1 query and 1 set of output logic?
> It doesn't seem trivial to me. TIA.
OK... say you have a setup like this
Item
=====
ItemID
ItemName
Cost
Zone
============
ZoneID
ZoneName
ItemShippingZone
============
ItemID
ZoneID
OverrideCost
ShippingCost
Where "Item.Cost" holds your item's regular cost;
ItemShippingZone.OverrideCost holds the 'override cost' for that
zone, and allows nulls (null = no override; you can have a boolean
field for this if you don't want to deal with nulls, or whatever).
Your query might be (in MS SQL anyway, otherwise I make no guarantees):
SELECT
some,
fields,
here,
COALESCE(ItemShippingZone.OverrideCost, Item.Cost) AS EffectiveCost,
some,
other,
fields
FROM
Item
INNER JOIN ItemShippingZone ON
(Item.ItemID = ItemShippingZone.ItemID)
INNER JOIN Zone ON
(ItemShippingZone.ZoneID = Zone.ZoneID)
ORDER BY
Item.ItemName,
Zone.ZoneName
The COALESCE means that if you don't have an
ItemShippingZone.OverrideCost entered, it will use the value from
Item.Cost in lieu of that.
Your grouping logic, I suspect, will be exactly the same....
for each record
{
if (itemid has changed)
{
write new item name
}
write zone name
write effective cost
write shipping cost
}
Does this make sense? Yell out if you need more help.
Cheers,
Paul
More information about the thelist
mailing list