How to check if specific datetime was not used using multiple tables?

Question

There are 3 tables: buildings, rooms, reservations.

1 building = n rooms
1 room = n reservations

TABLE BUILDINGS - ID(int), name(varchar)
TABLE ROOMS - ID(int), building_id(int)
TABLE RESERVATIONS - ID(int), room_id(int), date_start(datetime), date_end(datetime)

Buildings table example

<pre>
ID    name
1     Building A
2     Building B
3     Building C
</pre>

Rooms table example

<pre>
ID    building_id
1     1
2     1
3     2
4     3
</pre>

Reservations table example

<pre>
ID    room_id     date_start            date_end  
1     1           2014-08-09 14:00:00   2014-08-09 14:30:00
2     1           2014-08-09 14:30:00   2014-08-09 15:30:00
3     3           2014-08-09 16:30:00   2014-08-09 17:30:00
4     2           2014-08-09 16:00:00   2014-08-09 17:00:00
5     3           2014-08-09 16:00:00   2014-08-09 16:30:00
</pre>

Question

How to filter all buildings which have atleast 1 room available at the specific date and time?

For example we want to filter all buildings which have atleast 1 available room at 2014-08-09 16:00:00 until 2014-08-09 17:00:00. In this case it will show that Building A AND Building C are available. Building A has 1 free room and Building B also has 1 free room, because there is no reservation on that room.

Can someone help me out on this one? I just can not figure it out with a single MySQL query to get all buildings. Thank you.

Gordon Linoff · Accepted Answer · 2014-08-09 12:43:49Z

0

Use LEFT JOIN to connect the rooms table to the reservations table, with the time condition in the on clause. Then, look where there are no matches -- these are rooms that are available.

SELECT b.id, b.name, COUNT(*) as NumRoomsAvailable
FROM buildings b JOIN
     rooms r
     ON b.id = r.building_id LEFT JOIN
     reservations rv
     ON rv.room_id = r.id AND
        '2014-08-09 16:00:00' BETWEEN rv.date_start AND rv.date_end
WHERE rv.room_id is null
GROUP BY b.id;

The final step is just aggregating by the building, rather than listing each available room out separately.

answered Aug 9, 2014 at 12:43

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

user3924829 Over a year ago

Great answer, thank you! I have an additional question if possible. Can we filter buildings only by selected date (if it has atleast 1 available room on selected day in any hour)? Buildings working hours may be different.

Gordon Linoff Over a year ago

@user3924829 . . . I think that is more appropriate as a separate question. Nothing in the current question mentions building operating hours.

user3924829 Over a year ago

Ok, will do that. Thanks.

PravinS · Accepted Answer · 2014-08-09 12:28:36Z

0

Try this query

SELECT b.name,r.id,rv.date_start,rv.date_end FROM buildings b
LEFT JOIN rooms r ON b.id = r.building_id 
LEFT JOIN reservataions rv ON rv.room_id = r.id
WHERE  '2014-08-09 16:00:00' BETWEEN rv.date_start AND rv.date_end

answered Aug 9, 2014 at 12:28

PravinS

2,5943 gold badges22 silver badges27 bronze badges

Collectives™ on Stack Overflow

How to check if specific datetime was not used using multiple tables?

Buildings table example

Rooms table example

Reservations table example

Question

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

Buildings table example

Rooms table example

Reservations table example

Question

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related