Quote:
Originally Posted by Arsenal14
I'd add a subquery that returns the invoice total grouped by confirm_no and then join that to the rest of the query. So:
select reservation.confirm_no, datediff(day, res_checkin_date, res_checkout_date) * rate as basecost, invoicecost, basecost + invoicecost as totalcost
from reservation, price, room,
(select confirm_no, sum(money) as invoicecost from invoice group by confirm_no) inv
where room.price_plan = price.price_plan
and reservation.room_type = room.room_type
and inv.confirm_no = reservation.confirm_no
|
Thanks a lot for your help Arsenal, I'm going to try to incorporate these queries into my database when I get in tonight, I'll let you know how they work out. The logic there though makes sense to me, let's hope I can run it through!