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

i have 2 tables:

1. booking - records order detail

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

2. payment - records payment order

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

i want find bookings payments not complete. above data, expect answer 2,3, because sum of payments booking_id=1 matches corresponding booking_amount in booking_table.

to answer question, have 2 things need think :

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

2. you want join booking_amount table payment.

---

part 1 quite simple:

select sum(amount) totalp, booking_id payment group booking_id 

just basic query simple aggregate function...

---

for part 2, want join booking_amount , payment; basic join be:

select * booking b  left join payment p on b.id = p.booking_id 

we left join because may have booking not in payment table. bookings, null value. use coalesce replace null values 0.

---

the final query this:

select b.id, coalesce(totalp, 0),  b.booking_amount  booking b left join  (select sum(amount) totalp, booking_id payment group booking_id) t on  b.id = t.booking_id coalesce(totalp, 0) < b.booking_amount