I have the following problem - I've got a table full of shipments and another full of shipment detail lines which are the delivery locations for each shipment. Each location has a city which is also a key to another table full of cities that we ship to.

I also have a table full of rates with rate detail lines for each city included in the rate. The rate detail lines have a key to the cities table as well.

My problem is this when a shipment goes out I need to find the matching rate. I need to find the rate with the exact list of matching cities as the shipment for a given carrier. This is what I've got so far.

SELECT distinct [Rate Details].rate_key
FROM [Rate Details]
WHERE EXISTS

(SELECT Cities.city_key
FROM Cities INNER JOIN (Locations INNER JOIN [Runs Locations Carrier Out] ON Locations.Location =
[Runs Locations Carrier Out].Location) ON Cities.city_key = Locations.City_key

WHERE [Runs Locations Carrier Out].[Run Key] =2 AND Cities.city_key = [Rate Details].city_key)

This will find the intersection of the cities and rate details but it also returns rates that have just one of single cities and not the exact match and only the exact match of the cities or city on the shipment.

Am I pursuing this in the wrong fashion? Any help appreciated. Thanks.

Sounds like you need to do a Relational Division.

your query says "get all the rates in the rate details table, but only if a whole bunch of rows are all connected to something called Run Key 2, otherwise don't return anything at all"

that can't be right

i might be able to help you if you will follow along with me and build up your query one step at a time

you said "when a shipment goes out I need to find ..."

so let's start with a query that gets the particular shipment you're dealing with
is this at all close to what you're doing?

The relational division examples were exactly what I needed to perform here. Thanks for the help and the great link.