Reputation: 3188
I want to find the earliest date in a table. How do I put this into a join statement:
SELECT date FROM table1 WHERE orderno = 222 ORDER BY date LIMIT 1
Orderno 222
can have 1-* many rows in table1
that's why I'm using LIMIT 1
I have table these tables:
Order OrderLine ProductionDate
Order can have 1-* ProductionDates so when i do my join of ProductionDate i want to find the earliest date.
So my guess of a sql statement would be something like:
SELECT * FROM Order
LEFT JOIN (
IN SELECT date FROM ProductionDate ORDER BY date ASC LIMIT 1)
but this doesn't work. And i would like to know what i should change to get it to work?
Upvotes: 1
Views: 100
Reputation:
I'm not sure if MySQL supports a LIMIT In a derived table, but if it does, the syntax should be:
SELECT ord.*
FROM `Order` ord
LEFT JOIN (SELECT date FROM ProductionDate ORDER BY date ASC LIMIT 1) t
ON t.date = ord.date
Note that order
is a reserved word, so you will need to quote it properly. Either using MySQL dreaded backticks (see above) or - if you configured your server to be standard's compliant - using double quotest like this: "order"
Upvotes: 2
Reputation: 1516
SELECT `date`
FROM `table1`
INNER JOIN `table1`.`date` ON `table2`.`date`
ORDER BY date ASC LIMIT 1
Is that what you mean?
Upvotes: 0