How to return IDs ONLY of Properties that do not have existing booking from date range input by user?

Question

I'm setting up a property search on a vacation rental multi-property site. The search takes parameters for arrival date and departure date. The properties have multiple duplicate dates, since they're allowed to import multiple icals for each property to integrate bookings. How can I write a query that returns ONLY property IDs that do not have existing booked dates in reference to the user input?

Essentially, I'd like to return all properties that are available during the date range from user input.

Tried this.

SELECT p.name 
FROM property AS p
INNER JOIN property_calendar AS pc
ON p.id = pc.property_id
WHERE COALESCE('2018-11-17' NOT BETWEEN pc.start AND pc.end, TRUE)
      AND COALESCE('2018-11-19' NOT BETWEEN pc.start AND pc.end, TRUE)

DB Tables: (only showing relevant columns)

property

id | name 
-------------------------------------
1  | Beaches & Flows
2  | Mo Beaches Mo Problems
3  | 99 Problems and they all Beaches

property_calendar

id | property_id | start (Y-m-d) | end (Y-m-d) 
----------------------------------------------
1  | 1           | 2019-3-13     | 2019-3-17
2  | 1           | 2019-4-13     | 2019-4-17
3  | 1           | 2019-3-13     | 2019-3-17
4  | 1           | 2019-3-13     | 2019-3-17
5  | 2           | 2019-3-13     | 2019-3-17
6  | 3           | 2019-5-13     | 2019-5-17
7  | 3           | 2019-6-13     | 2019-6-17
8  | 3           | 2019-7-13     | 2019-7-17

Though there are duplicate dates for each property on the calendar table, I would only like it to return a property ID only once if it is available in that date range. If there are ANY occurrences where the date is not available for a property, then nothing should be returned in the query.

I really really appreciate any of the help I can get!

Sounds like you need a NOT EXISTS subquery with [date range overlap check](https://stackoverflow.com/questions/2545947/check-overlap-of-date-ranges-in-mysql) — Paul Spiegel, Apr 01 '19 at 19:01
@PaulSpiegel you mind throwing that together for me? I just gave it a shot, and I'm pretty sure I'm making a stupid mistake. — katracho7, Apr 01 '19 at 19:23
Your example (2018-11-17 - 2018-11-19) would return all rows, since there are no overlaps. Don't you have a better one? — Paul Spiegel, Apr 01 '19 at 19:29
@PaulSpiegel That's what I need help with! Lol, I really have no clue how to even approach this. But, you're absolutley right, it returns all rows, which is what I don't want. — katracho7, Apr 01 '19 at 19:35
I mean your dates **should** return all rows, since there is no overlap. — Paul Spiegel, Apr 01 '19 at 19:38

score 0 · Accepted Answer · answered Apr 01 '19 at 19:37

Check for existence of a date range overlap with a NOT EXISTS subquery:

set @new_start = '2019-06-16';
set @new_end   = '2019-06-19';

select *
from property p
where not exists (
  select *
  from property_calendar c
  where c.property_id = p.id
    and c.start < @new_end
    and c.end   > @new_start
)

Result:

id | name
---|-----------------------
1  | Beaches & Flows
2  | Mo Beaches Mo Problems

Property 3 is excluded, because the range (2019-6-13, 2019-6-17) overlaps with (2019-06-16, 2019-06-19).

Dependent on if you want to allow an overlap in a single day, you might need to change < and > to <= and >=.

Demo

How to return IDs ONLY of Properties that do not have existing booking from date range input by user?

1 Answers1