I am trying to get unreserved hotel rooms according to a daterange which is given by user.
I have two tables
$sql = "SELECT DISTINCT roomno
LEFT JOIN reservation ON rooms.room_id = reservation.room_id
(reservation.checkindate > '$newreservcheckin')
(reservation.checkindate > '$newreservcheckout')
(reservation.checkoutdate < '$newreservcheckin')
(reservation.checkoutdate < '$newreservcheckout')
You need to select the rooms which are either (A) Not booked or (B) Booked but outside the range you are querying for, below query should work:
select no from room r where not exists ( select id from reservation where room_id = r.id) union select no from room r where r.id not in ( select distinct room_id from reservation where checkindate between '2016-09-20' and '2016-09-22' OR checkoutdate between '2016-09-20' and '2016-09-22' )
Here is the SQL Fiddle.