Get latest record from second table left joined to first table
- by codef0rmer
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
