Mysql - Grouping the result based on a mathematical operation and SUM() function
- by SpikETidE
Hi all...
I'm having the following two tables...
Table : room_type
type_id type_name no_of_rooms max_guests rate
1 Type 1 15 2 1254
2 Type 2 10 1 3025
Table : reservation
reservation_id start_date end_date room_type booked_rooms
1 2010-04-12 2010-04-15 1 8
2 2010-04-12 2010-04-15 1 2
Now... I have this query
SELECT type_id, type_name
FROM room_type
WHERE id NOT IN (SELECT room_type
FROM reservation
WHERE start_date >= '$start_date'
AND end_date <= '$end_date')
What the query does is it selects the rooms that are not booked between the start date and end date.
Also, as you can see from the reservation table, we also have 'number of rooms booked between the two dates' factor also...
I need to add this 'no.of booked rooms between the two dates' factor also in to the query...
The query should return the type of rooms for which at least one room is free between the two dates.
I worked out the logic but just can't represent it as a query....! How will you do this...?
Thanks for your suggestions..!