CODEWITHSUNDEEP
phpmysqlsql
noob
noob

Reputation: 490

List unique rows while join two tables based on given date

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

Upvotes: 0

Views: 51

Answers (2)

Punitha Subramani
Punitha Subramani

Reputation: 1477

SELECT * FROM products p LEFT OUTER JOIN bookings b ON p.booth_number = b.booth_number where request_date = 'YYYY-MM-DD'

Upvotes: 0

Paul Maxwell
Paul Maxwell

Reputation: 35563

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

Upvotes: 1

Related Questions