wiwa1978 wiwa1978 - 5 months ago 9
SQL Question

SQL Query: find lots that belong to current auction

I have a table auctions and a table lots:

mysql> select id, auction_name, auction_startdate, auction_planned_closedate from auctions;
+----+--------------+---------------------+---------------------------+
| id | auction_name | auction_startdate | auction_planned_closedate |
+----+--------------+---------------------+---------------------------+
| 1 | Auction 1 | 2016-05-01 00:00:00 | 2016-06-30 00:00:00 |
| 2 | Auction 2 | 2016-06-01 00:00:00 | 2016-07-30 00:00:00 |
| 3 | Auction 3 | 2016-07-01 00:00:00 | 2016-08-30 00:00:00 |
| 4 | Auction 4 | 2016-09-01 00:00:00 | 2016-10-30 00:00:00 |
+----+--------------+---------------------+---------------------------+

mysql> select id, auction_id, lot_name from lots;
+----+------------+----------+
| id | auction_id | lot_name |
+----+------------+----------+
| 1 | 1 | Lot 1 |
| 2 | 1 | Lot 2 |
| 3 | 1 | Lot 3 |
| 4 | 1 | Lot 4 |
| 5 | 1 | Lot 5 |
| 6 | 1 | Lot 6 |
| 7 | 1 | Lot 7 |
| 8 | 2 | Lot 8 |
| 9 | 2 | Lot 9 |
| 10 | 2 | Lot 10 |
| 11 | 3 | Lot 11 |
| 12 | 3 | Lot 12 |
| 13 | 3 | Lot 13 |
| 14 | 3 | Lot 14 |
| 15 | 4 | Lot 15 |
| 16 | 4 | Lot 16 |
+----+------------+----------+


I want to display only the lots for current auctions (which are auctions 1 and 2 in the example), in other words for which the current time is between the 'auction_startdate' and 'auction_planned_closedate'.

So here is what I want to achieve:

+--------------+---------------------+---------------------------+---------+
| auction_name | auction_startdate | auction_planned_closedate | lots_id |
+--------------+---------------------+---------------------------+---------+
| Auction 1 | 2016-05-01 00:00:00 | 2016-06-30 00:00:00 | 1 |
| Auction 1 | 2016-05-01 00:00:00 | 2016-06-30 00:00:00 | 2 |
| Auction 1 | 2016-05-01 00:00:00 | 2016-06-30 00:00:00 | 3 |
| Auction 1 | 2016-05-01 00:00:00 | 2016-06-30 00:00:00 | 4 |
| Auction 1 | 2016-05-01 00:00:00 | 2016-06-30 00:00:00 | 5 |
| Auction 1 | 2016-05-01 00:00:00 | 2016-06-30 00:00:00 | 6 |
| Auction 1 | 2016-05-01 00:00:00 | 2016-06-30 00:00:00 | 7 |
| Auction 2 | 2016-06-01 00:00:00 | 2016-07-30 00:00:00 | 8 |
| Auction 2 | 2016-06-01 00:00:00 | 2016-07-30 00:00:00 | 9 |
| Auction 2 | 2016-06-01 00:00:00 | 2016-07-30 00:00:00 | 10 |
+--------------+---------------------+---------------------------+---------+


The following query gets me the current auctions:

mysql> select auction_name, auction_startdate, auction_planned_closedate from auctions where now() >= auction_startdate and now() <= auction_planned_closedate;
+--------------+---------------------+---------------------------+
| auction_name | auction_startdate | auction_planned_closedate |
+--------------+---------------------+---------------------------+
| Auction 1 | 2016-05-01 00:00:00 | 2016-06-30 00:00:00 |
| Auction 2 | 2016-06-01 00:00:00 | 2016-07-30 00:00:00 |
+--------------+---------------------+---------------------------+


and then I do an inner join with the 'lots' table:

select auction_name, auction_startdate, auction_planned_closedate, lots.id
from auctions
where now() >= auction_startdate
and now() <= auction_planned_closedate
inner join lots on auctions.id = lots.auction_id;



ERROR 1064 (42000): You have an error in your SQL syntax; check the
manual that corresponds to your MySQL server version for the right
syntax to use near 'inner join lots on auctions.id=lots.auction_id' at
line 1


I'm getting a syntax error which I'm staring blind at for a while.

Answer

Wrong order, put the WHERE clause after the JOIN:

select auction_name, auction_startdate, auction_planned_closedate, lots.id
from auctions
inner join lots on auctions.id = lots.auction_id
where now() >= auction_startdate
  and now() <= auction_planned_closedate