Returning only latest result from LEFT JOIN

Question

I am querying data from two tables (students2014 and notes2014) in order to return a list of students along with notes on each student. To do this I am using the following select statement:

SELECT * 
FROM students2014 
LEFT JOIN notes2014 
ON students2014.Student = notes2014.NoteStudent 
WHERE students2014.Consultant='$Consultant' 
ORDER BY students2014.LastName

This successfully gives me a list, however students with more than one note appear twice, for e.g:

Student a - Note
Student a - Note
Student b - Note
Student c - Note

etc...

I only want the latest note to appear for each student, thus delivering a list of each student only once.

Hope that makes sense?

hek2mgl · Answer 1 · 2013-10-23T15:34:28.823

1

You need to join the studends table with a sub query. Something like this should work:

SELECT * 
FROM `students2014`
LEFT JOIN (
    SELECT `note`, `NoteStudent`
    FROM `notes2014`
    HAVING `NoteID` = MAX(`NoteID`)
    GROUP BY `NoteStudent`
) `notes`
ON `students2014`.`Student` = `notes`.`NoteStudent`
WHERE `students2014`.`Consultant`='$Consultant' 
ORDER BY `students2014`.`LastName`

edited Oct 23 '13 at 15:34

answered Oct 23 '13 at 15:20

hek2mgl

152,036
28
249
266

I'm afraid it doesn't. Each note has a unique ID called NoteID which is an incremental numerical figure therefore latest note has higher ID number. Is it possible to work with that? – Phil Howell Oct 23 '13 at 15:28
@PhilHowell Ok, have changed the query. It now uses the `NoteID`. If it is an `auto_increment` as you say then it should work – hek2mgl Oct 23 '13 at 15:29
I had tried HAVING NoteID = MAX =(NoteID) but that doesn't appear to work... – Phil Howell Oct 23 '13 at 15:32
1

What means not work? means you receive no results? If so, can you execute just the sub query to see what it returns? – hek2mgl Oct 23 '13 at 15:34
Sorry, yes. There are no results I'm afraid. – Phil Howell Oct 23 '13 at 15:35
1

can you execute just the sub query to see what it returns? – hek2mgl Oct 23 '13 at 15:41

score 0 · Answer 2 · answered Oct 23 '13 at 15:29

Try (I don't test it, but must work):

SELECT *, MAX(notes2014.notesID) as maxnoteid 
FROM students2014 
LEFT JOIN notes2014 ON students2014.Student = notes2014.NoteStudent
WHERE students2014.Consultant='$Consultant' AND notes2014.notesID = maxnoteid 
GROUP BY students2014.ID
ORDER BY students2014.LastName

score 0 · Answer 3 · answered Oct 23 '13 at 15:39

0

select
  *
from
  `students2014`,
  `notes2014`
where
  `students2014`.`Student` = `notes2014`.`atudent`
  and `notes2014`.`id` in (
    select
    'NoteStudent`,
     max('NoteID`) as`MaxID`
  from
    `notes2014`
  group by
    `NoteStudent`
  )`

answered Oct 23 '13 at 15:39

Andrew

8,445
3
28
46

Returning only latest result from LEFT JOIN

3 Answers3

Linked