how to calculate a bill from several tables on mysql?

Question

I'm using mysql to create an hotel booking system, but i am struggling a little bit to calculate the final bill.

I need a SELECT command to get data from several tables and make some calculations.

Basically I just need to get the 'night cost' from a table called 'room_types'. Then, use DATEDIFF function to get the difference of days between the 'checkin' and 'checkout' columns in the table 'room_booking' and multiply the difference with the night cost and display the total.

These are the tables I would be using: are room_booking, room_types, booking, and room.

One booking may have several room bookings, so Im looking for a table that looks something like this:

+------------+------------+---------------+------------------+
| bookingid  | Room price | nights stayed | total room price |
+------------+------------+---------------+------------------+
| B001       | 30.00      | 4             |  120.00          |
+------------+------------+---------------+------------------+
| B001       | 40.00      | 3             |  120.00          |
+------------+------------+---------------+------------------+

booking id comes from table 'booking' room price from 'room_types', 'nights stayed' is calculated from the table room_booking, using the datediff command between checkin and checkout .

I hope i was clear

Answer 1

To be completely honest, you've just written your own query.

Just follow what you've said and you'll build it.

If you really can't accomplish it, please, post the table structure of the two tables.