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