tried it but it doesn't work in this case

if I change total_rooms of bookings for the record 1 to 4

`
SELECT r.id AS roomID, r.name, r.no_of_rooms, r.no_of_rooms - COALESCE(t.bookedCnt,0) AS available_rooms FROM rooms AS r LEFT JOIN ( SELECT room_id, COUNT(*) AS bookedCnt FROM bookings WHERE `start` <= '2015-08-14' AND `end` >= '2015-08-11' GROUP BY room_id ) AS t ON r.id = t.room_id
`

`
roomID	name	no_of_rooms	available_rooms
1	        Lake View	           4	3
2	        Royale	                  2	1
3	       Skyview	                  6	6
`

the data for the available rooms should be 0