Get latest record from second table left joined to first table

Question

I have a candidate table say candidates having only id field and i left joined profiles table to it. Table profiles has 2 fields namely, candidate_id & name.

e.g. Table candidates:

 id
 ----
 1
 2

and Table profiles:

 candidate_id      name
 ----------------------------
      1           Foobar
      1           Foobar2
      2           Foobar3

i want the latest name of a candidate in a single query which is given below:

SELECT C.id, P.name 
  FROM   candidates C
LEFT JOIN profiles P ON P.candidate_id = C.id 
GROUP BY C.id 
ORDER BY P.name;

But this query returns:

1     Foobar
2     Foobar3

...Instead of:

1     Foobar2
2     Foobar3

Answer 1

Use a subquery:

SELECT C.id, (SELECT P.name FROM profiles P WHERE P.candidate_id = C.id ORDER BY P.name LIMIT 1);

Answer 2

The problem is that your PROFILES table doesn't provide a reliable means of figuring out what the latest name value is. There are two options for the PROFILES table:

1. Add a datetime column IE: created_date
2. Define an auto_increment column

The first option is the best - it's explicit, meaning the use of the column is absolutely obvious, and handles backdated entries better.

ALTER TABLE PROFILES ADD COLUMN created_date DATETIME

If you want the value to default to the current date & time when inserting a record if no value is provided, tack the following on to the end:

DEFAULT CURRENT_TIMESTAMP

With that in place, you'd use the following to get your desired result:

   SELECT c.id, 
          p.name 
     FROM CANDIDATES c
LEFT JOIN PROFILES p ON p.candidate_id = c.id 
     JOIN (SELECT x.candidate_id,
                  MAX(x.created_date) AS max_date
             FROM PROFILES x
         GROUP BY x.candidate_id) y ON y.candidate_id = p.candidate_id
                                   AND y.max_date = p.created_date
 GROUP BY c.id 
 ORDER BY p.name

Answer 3

Thx guys, It works. But Sjoerd's query is faster than OMG Ponies. Thx anyways.