It would be something like this:
<pre>SELECT t1.id,t1.name,MAX(t2.date)
FROM table_1 t1 JOIN table_2 t2
ON t1.id = t2.id
GROUP BY t1.id,t1.name</pre>
——— kccrosser ———–
The answer above will work, but only if the only data desired from t2 is the date field. If you need any additional fields from t2, using GROUP BY and aggregate functions can be a pain.
I would use the following approach:
<pre>SELECT t1.id, t1.name, t2.date, t2.[otherstuff]
FROM table_1 t1
JOIN table_2 t2 ON t2.id = t1.id
WHERE NOT EXISTS (
SELECT 1 FROM table_2 t22
WHERE t22.id = t2.id AND t22.date > t2.date)</pre>
If there is a possibility that there are multiple t2 records with the same ID and Date values, then you would also want to qualify the results on some additional columns – like the primary key, e.g.:
<pre>SELECT t1.id, t1.name, t2.date, t2.[otherstuff]
FROM table_1 t1
JOIN table_2 t2 ON t2.id = t1.id
WHERE NOT EXISTS (
SELECT 1 FROM table_2 t22
WHERE t22.id = t2.id AND
(t22.date > t2.date OR (t22.date %3</pre>
Discuss This Question: 1  Reply