Question 112892 in Mysql Question posted in
The official MySQL documentation can be found here.

MySQL use data from 2 tables to show 1 table

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
Tags:

3

Answers


  1. 0

    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
    Login or Signup to reply.
  2. 0

    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.

    Login or Signup to reply.
  3. 0 
    SELECT 
    orderss.id, orderss.ORDER_TOTAL, orderss.CLIENT_ID,
    nvl(A.Total_paid,0) AS TOTAL_PAID,
    Orderss.ORDER_TOTAL-nvl(A.Total_paid,0) AS TOTAL_OUTSTANDING
FROM orders
LEFT JOIN
    (SELECT id,payment_status,Total_paid
     FROM order_paymentss
     GROUP BY id,payment_status,Total_paid
     HAVING Payment_status='paid'
    ) A 
    ON orderss.id=A.id;

    This should easily work.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search