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

[Paul Cowan]

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