Mysql - Grouping the result based on a mathematical operation and SUM() function

Question

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..!

Answer 1

Your result may be got in this

 SELECT a.type_id, a.type_name, a.no_of_rooms, (

SELECT SUM( booked_rooms )
FROM reservation
WHERE room_type = a.type_id
AND start_date >= '2010-04-12'
AND end_date <= '2010-04-15'
) AS booked_rooms, (
a.no_of_rooms - (
SELECT SUM( booked_rooms )
FROM reservation
WHERE room_type = a.type_id
AND start_date >= '2010-04-12'
AND end_date <= '2010-04-15' )
) AS freerooms
FROM room_type AS a
LEFT JOIN reservation AS b ON a.type_id = b.room_type
GROUP BY a.type_id
ORDER BY a.type_id