Remove duplicates from LEFT JOIN query

Question

I am using the following JOIN statement:

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

to retrieve a list of students (students2014) and corresponding notes for each student stored in (notes2014).

Each student has multiple notes within the notes2014 table and each note has an ID that corresponds with each student's unique ID. The above statement is returning a the list of students but duplicating every student that has more than one note. I only want to display the latest note for each student (which is determined by the highest note ID).

Is this possible?

possible duplicate of [Returning only latest result from LEFT JOIN](http://stackoverflow.com/questions/19545702/returning-only-latest-result-from-left-join) — halfer, Feb 24 '14 at 22:41
Hi there. Please don't ask your questions twice - it causes wasted effort that could be used more efficiently elsewhere. — halfer, Feb 24 '14 at 22:42

Juan Pablo Califano · Answer 1 · 2013-10-24T15:33:09.000

You need another join based on the MAX noteId you got from your select.

Something like this should do it (not tested; next time I'd recommed you to paste a link to http://sqlfiddle.com/ with your table structure and some sample data.

SELECT * 
FROM students s
LEFT JOIN (
    SELECT MAX(NoteId) max_id, NoteStudent
    FROM notes  
    GROUP BY NoteStudent
) aux ON aux.NoteStudent = s.Student
LEFT JOIN notes n2 ON aux.max_id = n2.NoteId

If I may say so, the fact that a table is called students2014 is a big code smell. You'd be much better off with a students table and a year field, for many reasons (just a couple: you won't need to change your DB structure every year, querying across years is much, much easier, etc, etc). Perhaps you "inherited" this, but I thought I'd mention it.

Sashi Kant · Answer 2 · 2013-10-24T14:24:54.963

1

GROUP the query by studentId and select the MAX of the noteId

Try :

SELECT 
students2014.Student,
IFNULL(MAX(NoteId),0)
FROM students2014 
LEFT JOIN notes2014 ON (students2014.Student = notes2014.NoteStudent) 
WHERE students2014.Consultant='$Consultant'
GROUP BY students2014.Student 
    ORDER BY students2014.LastName

edited Oct 24 '13 at 14:24

answered Oct 24 '13 at 14:19

Sashi Kant

13,277
9
44
71

I think we're getting closer, however this excludes any students from the list that do not have any notes. I need to display those that do not presently have any corresponding notes too. – Phil Howell Oct 24 '13 at 14:23
LEFT OUTER JOIN instead of just JOIN – Chris Albert Oct 24 '13 at 14:24
Think I spoke to soon. That has indeed got rid of the duplicates, and is showing all students (even those without notes) The only problem is that it is showing the first note entered rather than the note with the highest numerical ID. Do you know why that might be? – Phil Howell Oct 24 '13 at 14:34
It appears that the MAX(NoteID) isn't actually performing any function at all. I can remove it and still return the same results using LEFT OUTER JOIN... – Phil Howell Oct 24 '13 at 14:43
The GROUP BY is removing your duplicates. The MAX function is only showing you the highest NoteID available. The problem is that the notes table join is based on studentID. – Chris Albert Oct 24 '13 at 15:03
Is there anything I can do about that @ChrisAlbert? – Phil Howell Oct 24 '13 at 15:06
@PhilHowell Sorry I couldn't respond sooner. Juan posted a good example of how to solve the join issue. – Chris Albert Oct 24 '13 at 18:13

Remove duplicates from LEFT JOIN query

2 Answers2