Results 1 to 4 of 4
  1. #1
    Join Date
    Apr 2004
    Posts
    173

    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]
    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.

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Sounds like you need to do a Relational Division.

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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
    Code:
    select foo, bar
      from shipments
     where shipments.id = ?
    is this at all close to what you're doing?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Apr 2004
    Posts
    173
    The relational division examples were exactly what I needed to perform here. Thanks for the help and the great link.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •