Unanswered: Set Intersection Finding Only Exact Matches
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]
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.