CODEWITHSUNDEEP
mysqlsqldatabaseselect
rel1x
rel1x

Reputation: 2441

SQL query to check if column includes some data

I need to select all free rooms from hotel DB and I think I can do it with two steps:

  1. bookings = select * from booking where booking.startDate>=selectedStartDate and booking.endDate=<selectedEndDate.
  2. pseudo query: select * from room where room.room_id not includes bookings.room_id.

I wrote my second query like pseudo query because I can't find how I can do what I want to do. How to check that bookings doesn't includes room_id's?

My booking table looks like:

+-----------+------------+------------+---------+---------+
| bookingId | startDate  | endDate    | room_id | guestId |
+-----------+------------+------------+---------+---------+
|         1 | 2016-03-12 | 2016-03-22 |       1 |       1 |
|         2 | 2016-03-12 | 2016-03-22 |       2 |       2 |
+-----------+------------+------------+---------+---------+

Upvotes: 2

Views: 89

Answers (3)

Mureinik
Mureinik

Reputation: 311448

You could transform the first query to a subquery of the second query by using the not in operator:

SELECT *
FROM   room
WHERE  room.room_id NOT IN (SELECT room_id
                            FROM   booking
                            WHERE  startDate >= selectedEndDate AND
                                   endDate <= selectedStartDate)

Upvotes: 2

Hassan Naqvi
Hassan Naqvi

Reputation: 423

You can use a more optimized query

SELECT * FROM room JOIN booking ON room.room_id = booking.room_id
WHERE booking.startDate >= selectedStartDate AND booking.endDate <= selectedEndDate

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269953

If you want rooms free during a period of time, use not exists. The correct logic is:

select r.*
from room r
where not exists (select 1
                  from booking b
                  where $startdate <= b.enddate and $enddate >= b.startdate
                 );

Two periods overlap when one starts before the second ends and the first ends after the second starts.

Note that the <= and >= might be strict inequalities, depending on whether the first and last dates are included in the period.

Upvotes: 2

Related Questions