Avoid duplicates during joins

Question

Let's assume I've got this database:

table1:
- id
- name

and

table2:
-id
-name
-date
-table1id

If I do something like

SELECT table1.id, table1.name,table2.date
FROM table1
LEFT JOIN table2 ON table1.id = table2.table1id

I will have 2 rows.

I just would like to join the row from table2 with the most recent date. Is there a way to do it via MySQL, or must I do it with something like PHP after the query?

In fine I want one row, with table1.id, table1.name and the most recent date from the linked entries from table2.

Answer 1

Use GROUP BY and aggregation function MAX:

SELECT table1.id, table1.name, MAX(table2.date) AS `date`
FROM table1
LEFT JOIN table2 ON table1.id = table2.table1id
GROUP BY table1.id