search date ranges on MySQL-CodePudding

I want to find the products that do not have the date range that I pass (it will be a parameter). I was able to write this query, but it is limited because it does not consider a reservation with a previous "check in" or a later "check out".

Can you help me?

SELECT *
FROM bookings b
  right join products p ON b.product_id = p.id
WHERE b.check_in_date >= '2022-11-10' AND NOT b.check_out_date <= '2022-11-25';

The output would be all products that do not have any booking with dates within the range.

CodePudding user response：

I'm posting this as an answer because I can't comment, but how about:

SELECT *
FROM bookings b
  right join products p ON b.product_id = p.id
WHERE NOT (b.check_in_date >= '2022-11-10' AND b.check_out_date <= '2022-11-25');

Thus getting all the bookings which are not within the bounds ?

Note, this is equivalent to:

...WHERE b.check_in_date < '2022-11-10' OR b.check_out_date > '2022-11-25'

CodePudding user response：

Well, you're getting data from bookings, but you say you want products. So, I would go like this:

SELECT * FROM products p
JOIN bookings b ON b.product_id = p.id
WHERE b.productId NOT IN 
(SELECT bo.productId FROM bookings bo 
WHERE bo.check_in_date >= '2022-11-10'
AND b.check_out_date <='2022-11-25');

Didn't test it, but at least should give you an idea how to think about it.