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

    Unanswered: Matching two lists of keys

    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.

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Hi

    Quote Originally Posted by mmbosman
    ....not the one that matches the exact list of cities in the shipment....
    I'm afraid that sounds ominous. Could you post the structure of the shipment table and how "list of cities" fits into that (perhaps by showing some sample data).
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Apr 2004
    Posts
    173
    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.

    Clear as mud.

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by mmbosman
    Clear as mud.
    Indeed. If no one beats me to it I'll have another go tomorrow when I don't have a bottle of red wine in me. Ooh look - what a coincidence - a new emoticon


    Testimonial:
    pootle flump
    ur codings are working excelent.

  5. #5
    Join Date
    Apr 2004
    Location
    outside the rim
    Posts
    1,011
    ok, clear as mud about sums it up (I had some wine - it's not clearing it up).
    Let me try to rephase the question.

    You have a total rate for combinations of cities. You want to take a shippment and find the rate with the matching combination of cities.

    Or, in DB speak, you want to match two parent records that have matching child records (like the Brady Bunch, except all of his kids would have to be the same gender and same age as all of her kids).

    Shippment from NYC to Chicago to LA should only return the rate that has NYC to Chicago to LA. It should NOT return those with NYC and LA only, etc.

    If I restated the question correctly, then one way would be to create a key of sorts in the parent record based on the combination of keys from it's children.

    Perhaps a text string that is the combination of the keys of each city included in the detail, with each number seperated by a dash.

    Another option is to have some code that contructs an SQL statement, since we're actualy looking at a set of ANDs: Detail includes NYC and Chicago and LA and no others.

    Good luck!
    have fun!

    Todd S.
    click to email


    Independent Development Consultant
    Biz db & reports - SQL, Access, Crystal, Seradex and more
    Small, custom, unique programs - Office, VBA, .NET


    _________________________________________________
    Favorite message from Windows:
    There was an error displaying the previous error message

    Sadly, there was no error number to look up ...

Posting Permissions

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