I want to select all delivery stops for a driver. If 2 orders have the same delivery_id location it should only be one stop. Similarly if the pickup_id and delivery_id of different orders are the same it should also only be one stop. If an order has been 'Picked-up' there should be no pickup stop for it. As a seperate query afterwards I will select the orders to pick-up or deliver to the stop based on looping outcome and using the address_id.
orders table:
| order_id | pickup_id | delivery_id | driver_id | status | o_pickup | o_delivery |
|---|---|---|---|---|---|---|
| 1 | 4 | 2 | 1 | Assigned | 3 | 2 |
| 2 | 4 | 1 | 1 | Assigned | 3 | 1 |
| 3 | 2 | 5 | 1 | Assigned | 2 | 0 |
| 4 | 3 | 5 | 1 | Picked-up | 4 | 0 |
addresses table:
| address_id | street | lat | lat |
|---|---|---|---|
| 1 | 1 St | 40.332 | -70.332 |
| 2 | 9 Av | 40.942 | -70.942 |
| 3 | 4 St | 41.432 | -70.432 |
| 4 | 9 St | 41.321 | -70.047 |
| 5 | 0 Av | 43.031 | -72.321 |
Desired outcome:
| address_id | street | lat | lat |
|---|---|---|---|
| 4 | 9 St | 41.321 | -70.047 |
| 2 | 9 Av | 40.942 | -70.942 |
| 1 | 1 St | 40.332 | -70.332 |
| 5 | 0 Av | 43.031 | -72.321 |
The following query select the correct distinct pickup_id or delivery_id and organizes in the right order however doesn't combine stop with the same pickup_id and delivery_id, how would I select distinct pickup_id and delivery_id while taking into account order status?
SELECT a.pickup_id as address_id, b.street, b.lat, b.lon, a.o_pickup as o_order
FROM orders a
LEFT JOIN addresses b ON a.pickup_id = b.address_id
WHERE a.driver_id = 1
AND a.status = 'Assigned' GROUP BY a.pickup_id
UNION
SELECT a.delivery_id as address_id, b.street, b.lat, b.lon, a.o_delivery AS o_order
FROM orders a
LEFT JOIN addresses b ON a.delivery_id = b.address_id
WHERE a.driver_id = 1
AND (a.status = 'Assigned' OR a.status = 'Picked-up')
GROUP BY a.delivery_id
ORDER BY o_order DESC;
Lastly I will have to count the to do and completed number of stops. Would I be able to achieve with a count case query?
Use DISTINCT instead of GROUP BY. Otherwise your query was ok. You should only use GROUP BY with aggregate functions (sum, min, max, count...).
SELECT distinct a.pickup_id as address_id, b.street, b.lat, b.lon, a.o_pickup as o_order
FROM orders a
LEFT JOIN addresses b ON a.pickup_id = b.address_id
WHERE a.driver_id = 1
AND a.status = 'Assigned'
UNION
SELECT distinct a.delivery_id as address_id, b.street, b.lat, b.lon, a.o_delivery AS o_order
FROM orders a
LEFT JOIN addresses b ON a.delivery_id = b.address_id
WHERE a.driver_id = 1
AND (a.status = 'Assigned' OR a.status = 'Picked-up')
ORDER BY o_order DESC;
See dbfiddle.