Let 1 and 2 be desired start and end time. Let A and B be existing start and end time for a cleaner
Cleaner is free
1 2
A B
Cleaner is free
1 2
A B
Covering all of the above, which expressed with words would be: match all of a cleaner's existing bookings that are not in the desired range
B < 1 OR A > 2
or translated to fieldnames (most likley not matching your actual names though)
cleaner.endtime < wanted.starttime OR cleaner.starttime > wanted.endtime.
Note: The above logic expression is enough to deduce the logical expression for when user is not free, since
!(B < 1 OR A > 2) can be changed into
B > 1 AND A < 2
But let's examine it anyway
Cleaner is not free
1 2
A B
B > 1 and A < 2 (as predicted)
Cleaner is not free
1 2
A B
B > 1 and A < 2 (as predicted)
Cleaner is not free
1 2
A B
B > 1 and A < 2 (as predicted)
To understand how to use these results in combination with a left join, you need to first understand why your previous question resulted in the best possible query to solve it was using a left join (which you allready had) and adding a condition that the the joined table contained null (on any field)
To quickly reiterate. This select all cleaners that have no bookings at all.
SELECT fields
FROM cleaner c
INNER JOIN bookings b ON b.cleaner_id = c.id
WHERE b.id IS NULL
Thus, perform a left join with the added join condition that there is an existing bookings. The it's only the cleaners that do not have such a booking that will have null fields in the joined table. Which obviously happens to be those that are available during those times.
FROM cleaner c
LEFT JOIN bookings b WHERE b.cleaner_id = c.id AND b.end_time > @wanted_start_time AND b.start_time < @wanted_end_time
WHERE b.id IS NULL
