How is it possible to show all orders which are not completely paid off related to a client? for e.g.
SHOW ALL order details of ‘unpaid’ ORDERS for CLIENT ‘50’
table: orders
| id | order_total | client_id |
|---|---|---|
| 1 | 15.00 | 50 |
| 2 | 18.50 | 50 |
| 3 | 40.00 | 50 |
table: order_payments
| order_id | total_paid | payment_status |
|---|---|---|
| 1 | 15.00 | paid |
| 2 | 3.50 | open |
| 2 | 12.00 | paid |
*** NOTE: Not every order has a registered payment. No registered payment should be considered "open" ***
Expected results:
| order_id | order_total | client_id | outstanding |
|---|---|---|---|
| 1 | 15.00 | 50 | 0 |
| 2 | 18.50 | 50 | 6.50 |
| 3 | 40.00 | 50 | 40.00 |
I already have it working but with the help of PHP, looping through the data. But I would like to learn how to do it more efficiently with just sql.
CodePudding user response：
I tested your tables and I would do it like this:
SELECT o.id AS order_id, o.order_total, o.client_id,
(o.order_total - SUM(IF(op.payment_status = 'paid', op.total_paid, 0))) AS outstanding
FROM orders o
LEFT JOIN order_payments op ON o.id = op.order_id
GROUP BY o.id
ORDER BY outstanding ASC
CodePudding user response：
If you have a recent enough version of MySQL (>= 8.0.14), I would recommend a lateral join:
select o.*, op.total_paid, o.order_total - op.total_paid outstanding
from orders o
cross join lateral (
select coalesce(sum(op.total_paid), 0) total_paid
from order_payments op
where payment_status = 'open' and op.order_id = o.id
) op
The subquery after the lateral keyword searches the relevant rows in the payment table for each order, and returns the total paid (or 0 if there are no rows, or no valid payment).
Lateral joins are a powerful feature in SQL (that took a long time coming into MySQL). They are efficient, flexible and expressive, and come handy in many such situations.