How would one go about matching two lists of values in detail type tables? For example a list of (keyed) cities on a shipment to the list of (keyed) cities on a rate quote for shipping an order to look up the quote amount. Joins produce any rate quotes that have that city in them not the one that matches the exact list of cities in the shipment. I'm sure there has to be a simple answer to this, but I can't seem to grasp one.
Thanks for your reply - It works something like this
A shipment record has attached shipment detail lines which consist of a location, stop number on the shipment, case count for that detail line, etc. Each location has a key to the city it resides in. So each shipment has multiple locations with possibly the same cities or different cities.
A rate also has attached rate detail lines. A rate represents a quoted dollar amount for a given delivery route. That route may be for one city or may include several cities. The rate detail lines are basically the cities that are a part of the quoted rate.
The cities in both the shipment detail lines and the rate detail lines are numeric keys to city state combinations. A single city and state combination is only represented once in that table. So a shipment to Chicago, IL (if it were the only stop) has a parent record with load data and then a single stop with the key of the city. The quoted rate to Chicago IL has a parent rate record with the amount for that route and the city key for Chicago.
I'm wrestling with the following - finding the rate (if one exists) for a list of cities (actually key to cities) that are represented by a shipment. Basically finding a rate, with a list of city keys in the rate detail lines that is the same as the list of of city keys for the shipment detail lines in the shipment.