Join on other table with date range having count 0

Question

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.

score 3 · Accepted Answer · answered Nov 11 '10 at 23:43

3

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)

answered Nov 11 '10 at 23:43

Charles Bretana

143,358
22
150
216

Your date range detection is more elegant, +1 – zerkms Nov 11 '10 at 23:48
Thanks, check out http://stackoverflow.com/questions/325933/determine-whether-two-date-ranges-overlap/325964#325964 – Charles Bretana Nov 11 '10 at 23:53
Hehe, that explains everything ;-) – zerkms Nov 11 '10 at 23:54
Thanks, really simpler than I expected. But wouldn't it be more efficient to select null instead of * in the second part of query, like suggested by @zerkms ? – Tadas T Nov 12 '10 at 00:05
@Tadas Tamosauskas: if only just a litle ;-) – zerkms Nov 12 '10 at 00:08
1

@Tadas, No, It would not, The query processor interprets `Select *` within an Exists or Not Exists subquery as "Simply determine if a row is there". No actual data is actually returned. In fact, the processor stops as soon as it finds the first row that satisfies the subquery, and returns a true or false (depending of course on whether it is an Exists or a Not Exists) – Charles Bretana Nov 12 '10 at 03:17

score 1 · Answer 2 · answered Nov 11 '10 at 23:40

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)

score 0 · Answer 3 · answered Nov 11 '10 at 23:37

0

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

answered Nov 11 '10 at 23:37

Richard Marskell - Drackir

13,080
3
33
54

Join on other table with date range having count 0

3 Answers3