Need help with a multi-table query

[whitemank]

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.

[oddz]

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

[Dr John]

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?

[r937]

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