I have two tables like this

rooms

id | number
1  | 111
2  | 112

occupied_rooms

id | check_in   | check_out  | room_id
1  | 2017-10-01 | 2017-10-04 | 1

I want to get all the unoccupied rooms according to date check_in and check_out for this I tried

select rooms.id,rooms.number
    from `rooms` left join `occupied_rooms` on `rooms`.`id` = `occupied_rooms`.`room_id` 
    where (`occupied_rooms`.`check_in` not between "2017-10-05" and "2017-10-08" 
 or (`occupied_rooms`.`check_in` >= "2017-10-05" and `occupied_rooms`.`check_out` <= "2017-10-08"))

but this query giving me result like this. which is incorrect.

id  | number
1   | 111

What is wrong with this query? Thank you for your any help and suggestions

I have two tables like this

rooms

id | number
1  | 111
2  | 112

occupied_rooms

id | check_in   | check_out  | room_id
1  | 2017-10-01 | 2017-10-04 | 1

I want to get all the unoccupied rooms according to date check_in and check_out for this I tried

select r.id
     , r.number
  from rooms r
  left join occupied_rooms o
    on r.id = o.room_id
 where (o.check_in not between "2017-10-05" and "2017-10-08" )
 or (o.check_in >= "2017-10-05" and o.check_out <= "2017-10-08"))

but this query giving me result like this. which is incorrect.

id  | number
1   | 111

What is wrong with this query? Thank you for your any help and suggestions