Get data where MAX(date) < X

Question

I have two tables with a one-to-many relationship.

Table1
ID name email
Table2
ID table1_ID date

I need to get all the data from Table1 where :

MAX(date) from Table2 < "2016-01-01"

This doesn't work. Max is considered as "invalid" in where clause. What I did was :

SELECT Table1.name, Table1.email, tmp.maxdate
FROM Table1
JOIN (  SELECT  MAX(date) maxdate, table1_ID
        FROM    Table2
        GROUP BY table1_ID ) as tmp
    ON  tmp.table1_ID = table1.id
WHERE   tmp.maxdate < "2016-01-01"
AND     (other conditions)

So this works. BUT I think the performance is going to be awful - explain shows that all the Table2 is being read, and this table will grow a lot.

Any idea on how I could do it otherwise, or how to improve my current query performances ?

Answer 1

Try:

SELECT Table1.name, Table1.email, tmp.maxdate
FROM Table1
INNER JOIN (  SELECT  MAX(date) maxdate, table1_ID
        FROM    Table2
        GROUP BY table1_ID
        HAVING maxdate > "2016-01-01" ) as tmp
    ON  tmp.table1_ID = table1.id
WHERE   
AND     (other conditions)

Before, you just bringing back everyone from Table2 and join it with Table1. This will knock off all those without the maxdate > "2016-01-01" and do join on it with Table1.