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.

I also want to know `where table2.date` what??? – Luthando Ntsekwa Jul 27 '16 at 08:44 — Luthando Ntsekwa, Jul 27 '16 at 08:44
http://stackoverflow.com/a/2657495 and deal with tie-breaks – Drew Jul 27 '16 at 08:50 — Drew, Jul 27 '16 at 08:50

score 2 · Accepted Answer · answered Jul 27 '16 at 08:42

2

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

answered Jul 27 '16 at 08:42

Blank

12,308
1
14
32

Avoid duplicates during joins

1 Answers1