LEFT OUTER JOIN only most current record

Question

I have the following two tables:

t1

+----+------+-----+
| id | name | age |
+----+------+-----+
|  1 | bob  |  42 |
|  2 | dave |  12 |
|  3 | mary |  78 |
|  4 | kim  |  41 |
+----+------+-----+

t2

+----+-------+------------------+
| id | t1_id |       date       |
+----+-------+------------------+
|  1 |     1 | 12/26/2014 14:32 |
|  2 |     1 | 12/26/2014 16:32 |
|  3 |     1 | 12/27/2014 14:32 |
|  4 |     2 | 12/26/2014 15:32 |
|  5 |     4 | 12/26/2014 14:32 |
|  6 |     4 | 12/27/2014 14:32 |
+----+-------+------------------+

I wish to obtain the following results (only include the most current JOINed date from t2):

+----+------+-----+------------------+
| id | name | age |       date       |
+----+------+-----+------------------+
|  1 | bob  |  42 | 12/27/2014 14:32 |
|  2 | dave |  12 | 12/26/2014 15:32 |
|  3 | mary |  78 | null             |
|  4 | kim  |  41 | 12/27/2014 14:32 |
+----+------+-----+------------------+

The following gets me close, but JOINs all the matches from t2. How do I just include the most recent?

SELECT t1.id, t1,name, t1.age, t2.date
FROM t1
LEFT OUTER JOIN t2 ON t2.t1_id=t1.id;

what do you mean by most recent. You want to check for current date? — kamoor, Dec 27 '14 at 15:31
I think I got it. One record per user with most recent record? — kamoor, Dec 27 '14 at 15:33
@kamoor Yes, one record per user, and include the most recent joined date (or NULL if none). — user1032531, Dec 27 '14 at 15:34
possible duplicate of [SQL join: selecting the last records in a one-to-many relationship](http://stackoverflow.com/questions/2111384/sql-join-selecting-the-last-records-in-a-one-to-many-relationship) — RandomSeed, Dec 27 '14 at 15:53

unutbu · Answer 1 · 2014-12-27T15:45:37.933

2

mysql> SELECT t1.id, t1.name, t1.age, t3.max_date 
FROM t1 LEFT JOIN 
(SELECT t1_id, MAX(date) AS max_date FROM t2 GROUP BY t1_id) t3 
ON t1.id = t3.t1_id;

yields

+------+------+------+------------------+
| id   | name | age  | max_date         |
+------+------+------+------------------+
|    1 | bob  |   42 | 12/27/2014 14:32 |
|    2 | dave |   12 | 12/26/2014 15:32 |
|    3 | mary |   78 | NULL             |
|    4 | kim  |   41 | 12/27/2014 14:32 |
+------+------+------+------------------+
4 rows in set (0.00 sec)

edited Dec 27 '14 at 15:45

answered Dec 27 '14 at 15:34

unutbu

842,883
184
1,785
1,677

t3's SELECT clause should not have "id" column. – KSN Dec 27 '14 at 15:42
Thank you. Good to have choices! I will test both this solution and Gordon's solution before making a selection. – user1032531 Dec 27 '14 at 15:45

Gordon Linoff · Accepted Answer · 2014-12-27T15:49:36.207

2

Just use group by and max():

SELECT t1.id, t1.name, t1.age, MAX(t2.date) as date
FROM t1 LEFT OUTER JOIN
     t2
     ON t2.t1_id = t1.id 
GROUP BY  t1.id, t1.name, t1.age;

EDIT:

You might find this more convenient and it could have better performance:

SELECT t1.*,
       (SELECT MAX(t2.date)
        FROM t2
        WHERE t2.t1_id = t1.id
       ) as date
FROM t1;

For optimal performance, you want an index on t2(t1_id, date).

edited Dec 27 '14 at 15:49

answered Dec 27 '14 at 15:35

Gordon Linoff

1,242,037
58
646
786

Thank you. Good to have choices! I will test both this solution and unutbu's solution before making a selection. – user1032531 Dec 27 '14 at 15:44
Since t1.id is a PK (sorry, didn't say so in my original post), couldn't I just group by t1.id? – user1032531 Dec 27 '14 at 15:46
@user1032531 . . . Yes, but I prefer to be explicit about all columns used in the `group by`. Other databases do not support the full extensions that MySQL has to `group by`. However, using the `id` is supported by the standard; so it is a very reasonable thing to do. – Gordon Linoff Dec 27 '14 at 15:48

LEFT OUTER JOIN only most current record

2 Answers2