I am trying to get unreserved hotel rooms according to a daterange which is given by user. 

I have two tables

* **rooms** with columns room\_id, roomno
* **reservations** with columns reserv\_id, room\_id, checkindate, checkoutdate

Im applying this query

```
$sql = "SELECT DISTINCT roomno 
FROM rooms 
LEFT JOIN reservation ON rooms.room_id = reservation.room_id 
AND 
(
    (
        (reservation.checkindate > '$newreservcheckin') 
        AND
        (reservation.checkindate > '$newreservcheckout')
    ) 
    OR 
    (
        (reservation.checkoutdate < '$newreservcheckin') 
        AND 
        (reservation.checkoutdate < '$newreservcheckout')
    )
)";

```

**$newreservcheckin** and **$newreservcheckout** are date ranges provided by user

Let's say we have rooms with roomno as 100 and 101
now 100 is booked from 9-16-2016 to 9-18-2016 
and 100 is also booked from 9-27-2016 to 9-29-2016
and room 101 is unreserved(meaning it has no record in reservation table)

Suppose if user gives dates 9-26-2016 to 9-30-2016 query will ignore the room no 100 entry which is from 9-27-2016 to 9-29-2016 but will show the room no 100 with dates from 9-16-2016 to 9-18-2016 and will show 101 room 

How to mold the query so that it may not give the same room but the one with different dates?