php - 根据给定日期连接两个表时列出唯一行

Question

我有两张表，一张包含预订信息，另一张包含容器产品信息。下订单后，会在预订表中创建一条新记录。我想获得一个组合表，其中包含基于给定日期的两个表中的所有列。

表格:预订

 ------------------------------------------| booth_number| booking_date |    status   ||------------------------------------------||         1   |  2014-08-15  |    booked   ||         2   |  2014-09-10  |    booked   ||         3   |  2014-09-11  |    booked   | ------------------------------------------

表格:产品

 ---------------------------------------------------| booth_number|     desc     |    locked   |  type  ||------------------------------------------|--------||         1   | 2 x bottles  |    true     |  booth ||         2   | 2 x bottles  |             |  booth ||         3   | 4 x bottles  |             |  booth ||         4   | 4 x bottles  |             |  booth ||         5   | 5 x bottles  |             |  booth ||         6   | 5 x bottles  |             |  booth ||             |  $20 entry   |             | ticket | ---------------------------------------------------

查询后，当请求日期为2014-08-15时，我想得到如下结果

 -----------------------------------------------------------------------------| booth_number|     desc     |    locked   |  type  | request_date |  status  ||------------------------------------------|--------|--------------|----------||         1   | 2 x bottles  |    true     |  booth |  2014-08-15  |  booked  ||         2   | 2 x bottles  |             |  booth |              |          ||         3   | 4 x bottles  |             |  booth |              |          ||         4   | 4 x bottles  |             |  booth |              |          ||         5   | 5 x bottles  |             |  booth |              |          ||         6   | 5 x bottles  |             |  booth |              |          | -----------------------------------------------------------------------------

Answer 1

SELECT
      *
FROM products p
      LEFT JOIN bookings b
                  ON p.booth_number = b.booth_number
                        AND booking_date = '2014-08-15'
WHERE p.booth_number IS NOT NULL
ORDER BY p.booth_number
;

这将列出在某一天是否预订的所有展位