CODEWITHSUNDEEP
phpmysql
Abdelrahman Muntaser
Abdelrahman Muntaser

Reputation: 31

How can i simplify this query about reservation?

I have hotel reservation system, when the user enters a new reservation date with the period I need to retrieve all the unreserved rooms,

reservations table contain room_id, startdate, period

I wrote this query, just need to simplify it 

select * 
from rooms 
where id in (
   select room_id 
   from reservations 
   where '2018-6-11' not BETWEEN date_at and date_add(date_at, INTERVAL period day) 
   and '2018-6-30' not between date_at and date_add(date_at, INTERVAL period day) 
   and date_at not between '2018-6-11' and '2018-6-30' 
   and date_add(date_at ,INTERVAL period day ) not between '2018-6-11' and '2018-6-30' ) 
or id not in (select room_id from reservations)

Upvotes: 1

Views: 170

Answers (3)

Matthias S
Matthias S

Reputation: 3563

You don't need to do 2 IN queries. Since in your first IN query you have only NOT clauses, you can change those to NOT IN and remove the NOTs in the subquery. That will also remove the second OR IN.

select * 
from rooms 
where id not in (
   select room_id 
   from reservations 
   where '2018-6-11' BETWEEN date_at and date_add(date_at, INTERVAL period day) 
   or '2018-6-30' between date_at and date_add(date_at, INTERVAL period day)
   or date_at between '2018-6-11' and '2018-6-30' 
   or date_add(date_at ,INTERVAL period day ) between '2018-6-11' and '2018-6-30' ))

Basically this query translates to: Give me all rooms for which there is no reservation where my start- or end-date lies in between the reservation time.

EDIT:

Thanks to @vivek_23 I realized the query cannot be simplified in terms of the inner where conditions - nonetheless this version reduces the amount of queries to the database to 2.

Upvotes: 0

nice_dev
nice_dev

Reputation: 17805

Let's break this down into 2 sub problems.

  • Get all rooms which are never booked.
  • Get all rooms which never clash with booked rooms' timings.

In the query, I have assumed $start_date and $end_date are the PHP variables which have user selected values stored in them.

(select * 
from rooms where room_id NOT IN (select room_id from reservations))

UNION

(select *
from rooms where room_id IN (
   select room_id 
   from reservations 
   where $start_date not BETWEEN date_at and date_add(date_at, INTERVAL period day) 
   and $end_date not between date_at and date_add(date_at, INTERVAL period day)
   and ($start_date > date_add(date_at, INTERVAL period day) OR $end_date < date_at)
))

Upvotes: 0

XING
XING

Reputation: 9886

What about converting IN clause into equivalent joins? See below query if it works in your case:

SELECT *
FROM rooms rm
JOIN 
   (SELECT room_id rmid
  FROM reservations
  WHERE '2018-6-11' NOT BETWEEN date_at AND date_add(date_at, INTERVAL period DAY)
  AND '2018-6-30' NOT BETWEEN date_at AND date_add(date_at, INTERVAL period DAY)
  AND date_at NOT BETWEEN '2018-6-11' AND '2018-6-30'
  AND date_add(date_at ,INTERVAL period DAY ) NOT BETWEEN '2018-6-11' AND '2018-6-30'
  ) tb1  
ON  rm.ID = tb.rmid 
JOIN reservations rv
ON rm.ID <> rv.room_id;

Upvotes: 1

Related Questions