JOIN in combination with ORDER BY

Question

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?

Answer 1

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"

Answer 2

SELECT `date` 
FROM `table1` 
INNER JOIN `table1`.`date` ON `table2`.`date`
ORDER BY date ASC LIMIT 1

Is that what you mean?