mysql - Sql query to get data diffrence of total in 2 tables

Question

I have two tables:

1. booking - records the order detail

   id | booking_amount
   -------------------
   1  |            150
   2  |            500
   3  |            400
   

2. payment - records the payment for order

   id | booking_id | amount
   ------------------------
   1  |          1 |    100
   2  |          1 |     50
   2  |          2 |    100
   

I want to find all bookings where the payments are not complete. With the above data, we expect the answer to be 2,3, because the sum of payments for booking_id=1 matches the corresponding booking_amount in the booking_table.

Answer 1

To answer your question, you have 2 things you need to think about :

1. you want the total amount in your table payment by every booking row

2. you want to join your booking_amount table with payment.

---

Part 1 is quite simple:

SELECT sum(amount) as TotalP, booking_id FROM payment GROUP BY booking_id

Just a basic query with a simple aggregate function...

---

For part 2, we want to join booking_amount and payment; the basic JOIN would be:

SELECT * FROM booking b 
LEFT JOIN payment p ON b.id = p.booking_id

We do a LEFT JOIN because we may have some booking who are not in the payment table. For those bookings, you will get NULL value. We will use a COALESCE to replace the NULL values by 0.

---

The final query is this:

SELECT b.id, COALESCE(TotalP, 0),  b.booking_amount
FROM
 booking b
LEFT JOIN
 (SELECT sum(amount) as TotalP, booking_id FROM payment GROUP BY booking_id) as T
ON  b.id = T.booking_id
WHERE COALESCE(TotalP, 0) < b.booking_amount