To answer your question, you have 2 things you need to think about :
you want the total amount in your table payment
by every booking row
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
与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…