I've got this SQL query:

```
SELECT r.room_id AS room_id, 
       room_name, 
       num_rooms, 
       max_people, 
       min_rate 
FROM   room r 
       LEFT JOIN(SELECT b.room_id, 
                        accom_id, 
                        COALESCE(Count(b . room_id), 0) AS bookings 
                 FROM   booking b 
                 WHERE  arr_date >= :ind 
                        AND dep_date <= :outd 
                 GROUP  BY room_id) t 
              ON( t.accom_id = r.accom_id 
                  AND r.room_id = t.room_id ) 
WHERE  r.accom_id = :id 
       AND ( t.bookings < num_rooms 
              OR t.bookings IS NULL ) 
GROUP  BY room_name

```

Which relates to the following SQL Table:

![](https://i.stack.imgur.com/yAePw.png)

It has this information in booking table for testing purposes:
![](https://i.stack.imgur.com/1XkeJ.png)

The problem is:

When I run the query where :ind is 2014-07-27 and :outd is 2014-07-29 I get a return of nothing, which is exactly what I expect! However, if I change the dates to 2014-07-28(:ind) and 2014-07-29(:outd)

Both rooms are returned to me! Which is incorrect. Room\_id 2 should return to me, because the previous guest would have departed on the 28th, so 28-29 is free, but room\_id 1 shouldn't be available because guest is still staying in it until 29th.

How can I fix this logical error? Where is my SQL going wrong?

edit: "what does the sql actually do?" It is meant to check when there are bookings in rooms, and if the rooms have no bookings in them on the given dates, then they are free to be booked into, so it should return free rooms. Basically.