需要sql以获得酒店预订系统中的房间可用性

Question

Here my mysql database:

tbl_room

room_id || room_name || room_qty

tbl_reservation

reservation_id || check_in_date || check_out_date || room_id || reserved_qty

I need a sql query for summation of specific room reserved quantity in respect of customer given date(start and end).

I have tried below this.

SELECT sum( reserved_qty )
FROM `tbl_reservation`
WHERE ( 13/09/2014
BETWEEN check_in_date
AND check_out_date )
OR (
check_out_date
BETWEEN 11/09/2014
AND 13/09/2014
)
OR (
check_in_date
BETWEEN 11/09/2014
AND 13/09/2014
)
OR ( 11/09/2014
BETWEEN check_in_date
AND check_out_date )
AND room_id=1

But this is not working.

Answer 1

You tried find sum for field "no_of_room". But I can't see this field in tables. Maybe you need use other aggregate function here? Like sum(reserved_qty)?