hosselausso hosselausso - 6 months ago 6
SQL Question

MySQL query with left join and time conditions

I have the following tables:

mysql> select * from room;
+----+----------+
| ID | NAME |
+----+----------+
| 1 | Room 101 |
| 2 | Room 102 |
| 3 | Room 103 |
+----+----------+


mysql> select * from booking;
+---------+---------------------+---------------------+----------------+
| ROOM_ID | START | END | GUEST |
+---------+---------------------+---------------------+----------------+
| 1 | 2016-04-01 00:00:00 | 2016-04-30 00:00:00 | Dorian Gray |
| 1 | 2016-05-03 00:00:00 | 2016-05-20 00:00:00 | Vanessa Ives |
| 1 | 2016-05-21 00:00:00 | 2016-05-30 00:00:00 | Ethan Chandler |
| 2 | 2016-05-06 00:00:00 | 2016-05-18 00:00:00 | John Clare |
+---------+---------------------+---------------------+----------------+


For each room I'd like to get additional information about its booking status. If it is booked, I want to show the booking status. If it's not booked it has to show
NULL
.

If I start with a basic query..

mysql> select * from room r, booking b where r.id = b.room_id;
+----+----------+---------+---------------------+---------------------+----------------+
| ID | NAME | ROOM_ID | START | END | GUEST |
+----+----------+---------+---------------------+---------------------+----------------+
| 1 | Room 101 | 1 | 2016-04-01 00:00:00 | 2016-04-30 00:00:00 | Dorian Gray |
| 1 | Room 101 | 1 | 2016-05-03 00:00:00 | 2016-05-20 00:00:00 | Vanessa Ives |
| 1 | Room 101 | 1 | 2016-05-21 00:00:00 | 2016-05-30 00:00:00 | Ethan Chandler |
| 2 | Room 102 | 2 | 2016-05-06 00:00:00 | 2016-05-18 00:00:00 | John Clare |
+----+----------+---------+---------------------+---------------------+----------------+


It works OK, but that's not what I want.. let's see with left join...

mysql> select * from room r left join booking b on r.id = b.room_id;
+----+----------+---------+---------------------+---------------------+----------------+
| ID | NAME | ROOM_ID | START | END | GUEST |
+----+----------+---------+---------------------+---------------------+----------------+
| 1 | Room 101 | 1 | 2016-04-01 00:00:00 | 2016-04-30 00:00:00 | Dorian Gray |
| 1 | Room 101 | 1 | 2016-05-03 00:00:00 | 2016-05-20 00:00:00 | Vanessa Ives |
| 1 | Room 101 | 1 | 2016-05-21 00:00:00 | 2016-05-30 00:00:00 | Ethan Chandler |
| 2 | Room 102 | 2 | 2016-05-06 00:00:00 | 2016-05-18 00:00:00 | John Clare |
| 3 | Room 103 | NULL | NULL | NULL | NULL |
+----+----------+---------+---------------------+---------------------+----------------+


Still, it shows duplicates... Let's try with more conditions:

mysql> select * from room r left join booking b on r.id = b.room_id where b.start<now() and b.end>now();
+----+----------+---------+---------------------+---------------------+--------------+
| ID | NAME | ROOM_ID | START | END | GUEST |
+----+----------+---------+---------------------+---------------------+--------------+
| 1 | Room 101 | 1 | 2016-05-03 00:00:00 | 2016-05-20 00:00:00 | Vanessa Ives |
| 2 | Room 102 | 2 | 2016-05-06 00:00:00 | 2016-05-18 00:00:00 | John Clare |
+----+----------+---------+---------------------+---------------------+--------------+


Ok... but wrong, since 'Room 103' is not shown (it's not booked)...

I'd like to get something like this:

+----+----------+---------+---------------------+---------------------+--------------+
| ID | NAME | ROOM_ID | START | END | GUEST |
+----+----------+---------+---------------------+---------------------+--------------+
| 1 | Room 101 | 1 | 2016-05-03 00:00:00 | 2016-05-20 00:00:00 | Vanessa Ives |
| 2 | Room 102 | 2 | 2016-05-06 00:00:00 | 2016-05-18 00:00:00 | John Clare |
| 3 | Room 103 | NULL | NULL | NULL | NULL |
+----+----------+---------+---------------------+---------------------+--------------+


Any ideas?

Answer

I dont understand why you're using a datetime to store dates... but anyway...

SELECT * 
  FROM room r 
  LEFT 
  JOIN booking b 
    ON b.room_id = r.id
   AND CURDATE() BETWEEN b.start AND b.end;
Comments