Max date query

[safra]

Hello,

I can't firgure out how to do the following:

table 1:

customerid
name

table 2:

...
customerid
date

Customers can have multiple entries in table 2.

I want to find all customers for which the latest date in the date column is longer ago then 30 days.

I belief to return the latest date you can use MAX(date) and the 30 days interval can be checked like: date < NOW() - INTERVAL 30 DAY

But how to put the two together in a single query?

Any help would be very much appreciated, thanks!

[Weedpacket]

You'd use MAX, of course, GROUP BY customerid, and your "30 day" test would go in the HAVING clause.
Consult your DBMS's documentation for more.

[safra]

Thanks Weedpacket, I belief I got it working:

SELECT table1.customerid as id, table1.name FROM table1
LEFT JOIN table2 on table1.customerid=table2.customerid
GROUP BY table2.customerid HAVING MAX(table2.date)<now() - INTERVAL 30 DAY