MySQL: NOT BETWEEN not behaving as expected

Question

I have a booking system and I have to check if a date or a date range is not in between of date ranges saved in MySQL.

Here is my table:

bookings(id, property_id, booked_from_date, booked_to_date)

Each property can have none, one or multiple dates booked.

My query to check if a certain date or range is not in between one of the booked periods is

SELECT p.id, p.title
FROM properties p 
LEFT JOIN bookings b ON p.id = b.property_id 
WHERE 
    COALESCE('selected_from_date' NOT BETWEEN p.booked_from_date AND b.booked_to_date , TRUE) AND 
    COALESCE('selected_to_date' NOT BETWEEN p.booked_from_date AND b.booked_to_date , TRUE)
GROUP BY main.id

My logic is that if a certain date is not in between a booked range then it is available but the query does not return the correct results.

Is there any way of excluding all properties that are available between those dates ?

Thank you

EDIT

Here is the table which holds the booked ranges

id boat_id booking_id booked_from_date booked_to_date
 5      22 NULL       2016-05-28       2016-06-04
 6      22 NULL       2016-07-02       2016-07-09
 7      22 NULL       2016-07-16       2016-07-23
 9      25 NULL       2016-06-04       2016-06-11

Here is the parent table

 id title
 21 Vacanza tra comfort e benessere su barca a vela ad...
 22 Vacanza in Barca a Vela su Gib Sea 372
 23 Vacanza da sogno su un Motor Yacht
 24 Capri & Costiera Amalfitana
 25 Golfo di Napoli in barca a vela
 26 Il 44 piedi che sorprende

What i expect:

• If I search for 2016-05-30 I expect to return all boats except id 22, because the date 2016-05-30 is between a range already booked.

• If I search from 2016-06-05 to 2016-06-10 I expect to return all boats except id 25

Hope this helps

Answer 1

This query will find all not booked items from properties. Main idea is to search booked items (and place condition for it in JOIN bookings). Then transform JOIN into LEFT JOIN with WHERE bookings.id IS NULL - it will reverse condition from booked to not booked.

SELECT p.id, p.title
FROM properties p 
LEFT JOIN bookings b ON p.id = b.property_id
  AND b.booked_from_date <= 'selected_to_date'
  AND b.booked_to_date >= 'selected_from_date'
WHERE b.id IS NULL