CODEWITHSUNDEEP
sqlmysql
Tadas T
Tadas T

Reputation: 2637

Join on other table with date range having count 0

I have two associated tables (simplified schema)

mysql> describe booking;
+----------------+------------+
| Field          | Type       |
+----------------+------------+
| id             | int(11)    |
| room_id        | int(11)    |
| date_from      | date       |
| date_to        | date       |
+----------------+------------+   

mysql> describe room;       
+-------------+------------+
| Field       | Type       |
+-------------+------------+
| id          | int(11)    |
| isDouble    | tinyint(1) |
+-------------+------------+

What I need to do is get all the double (isDouble = true) rooms which are not booked during specified period (date_from, date_to) of time. I would like to get the result in one query.

Thanks for help.

Upvotes: 0

Views: 483

Answers (3)

Charles Bretana
Charles Bretana

Reputation: 146469

try:

Select Distinct id RoomId
From room r
Where isDouble = 1
 And Not Exists
   (Select * From booking
    Where room_id = r.id
      And date_from <= @EndDateRange
      And date_to >= @StartDateRange)

Upvotes: 3

zerkms
zerkms

Reputation: 254916

SELECT *
  FROM room r
 WHERE r.isDouble
   AND NOT EXISTS (SELECT NULL
                     FROM booking b
                    WHERE (date_from BETWEEN 'date1' AND 'date2'
                       OR date_to BETWEEN 'date1' AND 'date2')
                      AND b.room_id = r.id)

Upvotes: 1

Richard Marskell - Drackir
Richard Marskell - Drackir

Reputation: 13302

Try this: SELECT * FROM room WHERE isDouble = 1 AND id NOT IN (SELECT room_id FROM booking WHERE date_to BETWEEN 'date1' AND 'date2')

Upvotes: 0

Related Questions