MySQL left join with where condition

Question

I have two tables like this

rooms

id | number
1  | 111
2  | 112

occupied_rooms

id | check_in   | check_out  | room_id
1  | 2017-10-01 | 2017-10-04 | 1

I want to get all the unoccupied rooms according to date check_in and check_out for this I tried

select r.id
     , r.number
  from rooms r
  left join occupied_rooms o
    on r.id = o.room_id
 where (o.check_in not between "2017-10-05" and "2017-10-08" )
 or (o.check_in >= "2017-10-05" and o.check_out <= "2017-10-08"))

but this query giving me result like this. which is incorrect.

id  | number
1   | 111

What is wrong with this query? Thank you for your any help and suggestions

What is the default value of check_in , check_out ? is that null ? — Lawakush Kurmi, Nov 03 '17 at 06:34

score 2 · Accepted Answer · answered Nov 03 '17 at 06:34

2

Just join the two tables on the condition that the id matches and the range of intended stay overlaps with the range in the occupied_rooms table.

SELECT r.*
FROM rooms r
LEFT JOIN occupied_rooms t
    ON r.id = t.id AND
       ('2017-10-02' <= t.check_out AND '2017-10-03' >= t.check_in)
WHERE
    t.id IS NULL;   -- NULL value indicates the room did not overlap
                    -- with any existing reservations

You can also check out this great reference question on how to deal with overlapping ranges in SQL queries. It makes the problem you are facing much simpler.

Demo

answered Nov 03 '17 at 06:34

Tim Biegeleisen

502,043
27
286
360

thanks I added "is null" and it worked as expected when query is fired from php script but it still giving two result when query is run in phpmyadmin it supposed to give only one result -> select rooms.number from `rooms` left join `occupied_rooms` on `rooms`.`id` = `occupied_rooms`.`room_id` where (`occupied_rooms`.`check_in` not between "2017-10-02" and "2017-10-08" or (`occupied_rooms`.`check_in` >= "2017-10-02" and `occupied_rooms`.`check_out` <= "2017-10-08")) or `occupied_rooms`.`id` is null – sanu Nov 03 '17 at 07:00
Edit my demo, update it and paste the link here. I am guessing your data is the problem. – Tim Biegeleisen Nov 03 '17 at 07:02

score 0 · Answer 2 · answered Nov 03 '17 at 06:35

0

Your data in table occupied_rooms meets the first condition in "where";

check_in date(2017-10-01) is not between "2017-10-05" and "2017-10-08" and your where is or.

Thus, the result is included this data.

Can you tell us what output you expect?

answered Nov 03 '17 at 06:35

Alden Ken

142
8

Overlapping ranges are a bit much complex than this, q.v. here: https://stackoverflow.com/questions/325933/determine-whether-two-date-ranges-overlap – Tim Biegeleisen Nov 03 '17 at 06:36

MySQL left join with where condition

2 Answers2

Demo