[Mad Mel]

If I understand your data correctly, I'd approach it like this:

It appears that confirm_no on the reservation table is the primary key that you can use to join in most of the other tables.

So you should be able to join guest reservation to reservation using the confirm_no. Pretty straight forward.

Similarly, you should be able to join invoice to reservation on that column. However, invoice will potentially have zero records, or many records. What you need to do is get the total invoice, and join that. To do so, create a subquery (perhaps call it total_invoice) which is invoice grouped by confirm_no, and it sums inv_amount. Write the sql outside your main query and test it; it should return a row for each confirm_no with an amount. Add total_invoice to your view, and join on confirm_no. You will need to use an outer join, so that if there are no invoices associated with the reservation, the row will still show up in your view (it will have nulls for the invoice fields).

Then join price plan in onto the reservation table. I'm not sure of your data, you may just need to use price_plan, or both price_plan and default_villa_type.

Take a look at the output of your view after each join, and check that it has the columns and data that you need. At the end, add a column with your calculation.