CODEWITHSUNDEEP
mysqlsqlselectjoin
Phil Howell
Phil Howell

Reputation: 79

Remove duplicates from LEFT JOIN query

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?

Upvotes: 2

Views: 7119

Answers (2)

Juan Pablo Califano
Juan Pablo Califano

Reputation: 12333

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.

Upvotes: 4

Sashi Kant
Sashi Kant

Reputation: 13465

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

Upvotes: 1

Related Questions