CODEWITHSUNDEEP
mysqlsql
Rbijker.com
Rbijker.com

Reputation: 3114

How to filter out records from one table where its id occurs in a column of another table

I have 2 MySQL tables

tableRooms contains the rooms of a hotel

tableRoomsBooked contains the booked dates of the rooms

enter image description here

I need an SQL query that returns the rooms that have no bookings between 2 given dates. This is what I have got so far:

SELECT * FROM `tableRooms` 
LEFT JOIN `tableRoomsBooked`
ON `tableRooms`.`id` = `tableRoomsBooked`.`room_id` 

WHERE (date BETWEEN '2015-01-02' AND '2015-01-30')
....?

The query should only get the room_id 2 because room 2 has no bookings in this period.

What should my query be like?

Upvotes: 3

Views: 2239

Answers (1)

DooKie
DooKie

Reputation: 399

   select * 
   from tableRooms 
   where id not in (
     select distinct room_id 
     from tableRoomsBooked 
     where date between '2015-01-02' and '2015-01-30'
   )

This will select the list of existing IDs in a sub request, then exclude them from the main request.

Anyway, you should change the name of "date" column, because "date" can be confusing as soon as it is a data type too.

Upvotes: 6

Related Questions