CODEWITHSUNDEEP
mysqlleft-join
sanu
sanu

Reputation: 1068

MySQL left join with where condition

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

Upvotes: 0

Views: 52

Answers (2)

Alden Ken
Alden Ken

Reputation: 142

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?

Upvotes: 0

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522741

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

Upvotes: 2

Related Questions