0

I am pretty new to Postgresql so please bare with me. I am trying to inner join three tables that have the structure below. The trips table has the trip_id which the tables trip_pickup and trip_dropoff have multiple entries of. Each trip has multiple pickup and dropoffs but one driver and one trailer/truck. What I am trying to display is the trip_id and the first pickup and last dropoff. The query I have now displays all the combinations of the pickup and dropoffs. Is there a technique that I don't know of that could help me in this? I would greatly appreciate it if you could help.

trips

trip_id truck_id diver_id
1 56 15

trip_pickup

p_date order_number p_city p_state
01/05/2022 1 Houston TX
01/08/2022 2 Surprise AZ
01/09/2022 3 Phoenix AZ

trip_dropoff

d_date order_number d_city d_state
01/15/2022 1 NOGALES AZ
01/17/2022 2 Glendale MI
01/22/2022 3 Las Vegas NV
SELECT trips.trip_id, trip_pickup.p_date, trip_pickup.p_city, trip_pickup.p_state, 
trip_dropoff.d_date, trip_dropoff.d_city, trip_dropoff.d_state, trip_dropoff.d_date, 
trips.truck_id, drivers.first_name, drivers.last_name

FROM trips INNER JOIN trucks ON trucks.truck_id = trips.truck_id

INNER JOIN drivers ON drivers.driver_id = trips.driver_id

INNER JOIN trailers ON trailers.trailer_id = trips.trailer_id

INNER JOIN trip_pickup ON trip_pickup.trip_id = trips.trip_id

INNER JOIN trip_dropoff ON trip_dropoff.trip_id = trips.trip_id

ORDER BY trip_pickup.p_date DESC LIMIT 20; 

Desired Example Output:

trip_id truck_id driver_id p_date p_city p_state d_date d_city d_state
1 56 15 01/05/2022 Houston TX 01/22/2022 Las Vegas NV
5
  • Please define what a "first pickup" and "last dropoff" are, in terms of the columns in your table(s). Data in SQL tables are not intrinsically ordered. Example output would be helpful. Thanks!
    – dwhitemv
    Oct 30, 2022 at 1:18
  • Hello, the "first pickup" is the record with the earliest date and the "last dropoff" is the record with the latest date or furthest date from the first pickup. I have included an example output above for more clarity. Thank you
    – sandsina
    Oct 30, 2022 at 3:19
  • Thank you very much for your answer and that would work perfectly but sometimes there are trips that have different drop off with the same date. In that case I restructured my database to include the order which the dropoff and and pickups should be in. I hope this helps.
    – sandsina
    Oct 30, 2022 at 16:49
  • I have updated the query to use order_number.
    – dwhitemv
    Oct 31, 2022 at 2:53
  • Thank you so much. It worked perfectly, much appreciate it.
    – sandsina
    Oct 31, 2022 at 4:06

1 Answer 1

0

To get everything in one row, I used filtered aggregates on top of a union of the trip_pickup and trip_dropoff tables with a direction indicator.

I assumed that there is supposed to be a trip_id column in trip_pickup and trip_dropoff that is not in the example tables (but appears in your working query).

If the tables are very large, and trip_pickup and trip_dropoff have indexes over trip_id,{p,d}_date, then replacing the CTE self-join with the original tables might improve performance. The benefit increases if you ever want to query a subset of trip_ids.

No version of PostgreSQL was provided, so I assume you are running a supported version, which will have the necessary features for this query.

WITH cte AS (
SELECT 'p' AS direction, trip_id, p_date AS leg_date, p_city AS city, p_state AS state, order_number
  FROM trip_pickup
UNION ALL
SELECT 'd', trip_id, d_date, d_city, d_state, order_number
  FROM trip_dropoff
)

SELECT trips.trip_id,
    trips.truck_id,
    trips.driver_id,
    cte_1.leg_date AS p_date,
    cte_1.city AS p_city,
    cte_1.state AS p_state,
    cte_2.leg_date AS d_date,
    cte_2.city AS d_city,
    cte_2.state AS d_state
 FROM (
    SELECT cte.trip_id,
    min(order_number) FILTER (WHERE direction='p') AS first_pickup_seq,
    max(order_number) FILTER (WHERE direction='d') AS last_dropoff_seq
    FROM cte
    GROUP BY cte.trip_id) t
JOIN trips USING (trip_id)
JOIN cte cte_1 ON (cte_1.direction='p' AND t.trip_id=cte_1.trip_id AND t.first_pickup_seq=cte_1.order_number)
LEFT OUTER JOIN cte cte_2 ON (cte_2.direction='d' AND t.trip_id=cte_2.trip_id AND t.last_dropoff_seq=cte_2.order_number)
ORDER BY p_date DESC LIMIT 20

UPDATE

Altered query to use trip order_number instead of date. This should address tie-breaking any same-day pickups or dropoffs. For the record, the same could have been accomplished by recording pickup and dropoff time as timestamps.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.