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

Question

I have two tables, one contains booking information another one container product information. When orders placed, a new record created in the booking table. I want to get a combined table with all columns from the two tables based on the give date.

table: bookings

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

table: products

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

After query, I want to get a result like below when request date is 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
;

This will list all booths if booked or not on a certain day