Need help with a multi-table query
I'm trying to write a query that combines two tables of information:
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.