MySQL 2列查询

Question

我正在为我的酒店房间开发一个预订系统(PHP-MySQL)。

我有两张桌子

rooms_table

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

rooms_avl_table (我正在为房间保存不可用的天数)

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

我想列出rooms_avl_table表中没有列出的所有房间。即如果搜索与日期"2018-09-05"，需要显示所有房间与126 (room_id) -这是不可用的。

Answer 1

这应该是可行的：

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

Answer 2

除了@isaace提供的LEFT JOIN with checking for NULL解决方案之外，您还可以使用Correlated Subquery with NOT IN子句：

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'
                        )

Answer 3

使用不存在

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'
                        )