SitePoint Sponsor

User Tag List

Results 1 to 4 of 4
  1. #1
    SitePoint Zealot
    Join Date
    Jan 2006
    Posts
    169
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Need help with a multi-table query

    I'm trying to write a query that combines two tables of information:

    "shipments" table:
    user_id
    shipment_id
    package weight

    "waypoints" table:
    shipment_id
    waypoint_id
    waypoint_zip

    For a given shipment, there are multiple waypoints. The waypoint with the highest waypoint_id for a given shipment_id is the origin of the shipment. The waypoint with the lowest waypoint_id for a given shipment_id is the destination.

    I'm trying to write a query that returns a list of shipments for a given user with the following fields:

    package_weight, origin_zip, destination_zip

    Where origin_zip is the waypoint_zip with the highest waypoint_id and the dest_zip is the waypoint_zip with the lowest waypoint_id.

    Can someone help me out with this? I'm struggling with INNER JOIN and how to tie all this together.

  2. #2
    SitePoint Wizard bronze trophy
    Join Date
    Jul 2006
    Location
    Augusta, Georgia, United States
    Posts
    4,048
    Mentioned
    16 Post(s)
    Tagged
    3 Thread(s)
    Code SQL:
    SELECT
         s.package_weight
         ,so.waypoint_zip AS origin_zip
         ,sd.waypoint_zip AS destination_zip
      FROM
         shipments s
     INNER
      JOIN
         (SELECT
               w.shipment_id
               ,MAX(w.waypoint_id) AS shipment_origin
               ,MIN(w.waypoint_id) AS shipment_destination
            FROM
               waypoints w
           GROUP
              BY
               w.shipement_id) AS w
        ON
         s.shipment_id = w.shipment_id
     INNER
      JOIN
        waypoints so
       ON
        s.shipment_id = so.shipment_id
      AND
        w.shipment_origin = so.waypoint_id
    INNER
     JOIN
        waypoints sd
       ON
        s.shipment_id = sd.shipment_id
      AND
        w.shipment_destination = sd.waypoint_id

  3. #3
    SitePoint Wizard
    Join Date
    Jul 2003
    Location
    Kent
    Posts
    1,921
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    How do you know what value to give your origin waypoint to make it it highest value?

    What is a shipment goes A->B->C->D->E, but another one goes D->B->A???

    Or are you going to re-use the same waypoint under a different name for different trips?

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,017
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    dr john, it looks like the waypoint_ids are sequential within each shipment

    thus, shipment 157 (A-B-C-D-E) has waypoints 5-4-3-2-1 while shipment 158 (D-B-A) has waypoints 3-2-1

    the waypoint ids would not be comparable across shipments, which is why the zip has to be carried in that table too

    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

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