MySQL 2 column query

Question

I am developing a booking system (PHP-MySQL) for my hotel rooms.

I got 2 tables

rooms_table

----------+---------------+----------------+
room_id   |   room_name   |   room_type    |
----------+---------------+----------------+
125       |   name 2      |    deluxe      |
----------+---------------+----------------+
126       |   name 3      |    deluxe      |
----------+---------------+----------------+

rooms_avl_table ( in which I am saving non-available days for rooms)

----------+---------------+----------------+
avl_id    |  room_id      |   navl_date    |
----------+---------------+----------------+
12        |    125        | 2018-10-02     |
----------+---------------+----------------+
13        |    125        | 2018-09-05     |
----------+---------------+----------------+

I wanna list all rooms which are not listed in the rooms_avl_table table. ie if searching with a date "2018-09-05", needs to show all the rooms with out 126 (room_id) - which is not available.

score 0 · Answer 1 · answered Sep 05 '18 at 17:34

0

This should work:

select * from rooms_table r
left join rooms_avl a on r.room_id = a.room_id and a.navl_date = '2018-09-05'
where a.room_id is null

answered Sep 05 '18 at 17:34

isaace

3,336
1
9
22

score 0 · Answer 2 · answered Sep 05 '18 at 17:37

Besides the LEFT JOIN with checking for NULL solution by @isaace, you can also use a Correlated Subquery with NOT IN clause:

SELECT * FROM rooms_table AS rt 
WHERE rt.room_id NOT IN (SELECT rat.room_id 
                         FROM rooms_avt_table AS rat 
                         WHERE rat.room_id = rt.room_id 
                           AND rat.navl_date = '2018-09-05'
                        )

score 0 · Answer 3 · answered Sep 05 '18 at 17:41

use not exists

SELECT * FROM rooms_table AS r
WHERE r.room_id NOT EXISTS (SELECT rv.room_id 
                         FROM rooms_avt_table AS rv
                         WHERE rv.room_id = r.room_id 
                           AND rv.navl_date = '2018-09-05'
                        )

MySQL 2 column query

3 Answers3