MySQL limiting within a LEFT JOIN

Hi so i have a database with the multiple tables with different records like so,

- table_1
    - student_name
    - student_id
    - id
    - note
    - ...

- table_2
    - id
    - leave
    - ...

leave currently contains the records of all the days the student has taken day off.

sample data for table_2 as requested

So my question is how do i LEFT JOIN the tables and return the last leave date for the student instead of fetching all the leave date’s for that particular student.

here’s the current output that i’m getting

Here is my expected output.

All help is appreciated and thanks in advance.

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 1

As Martin already commented, you need to use an aggregate query. Since dates are effectively ordered lexicographically, the most recent date in a set of dates is its maximum. Therefore, you need to use the MAX() aggregator. Since you want to get the most recent date per student, you need to group the query by student. And since MySQL usually does not want you to print columns that are not in the GROUP BY clause, you’ll need to add all columns from TABLE_1 to that clause. This should do what you want:

SELECT TABLE_1.NAME, TABLE_1.ID, TABLE_1.NOTE, MAX(TABLE_2.LEAVEDATE) AS last_absent
FROM TABLE_1 LEFT JOIN TABLE_2 ON TABLE_1.ID = TABLE_2.ID
GROUP BY TABLE_1.ID, TABLE_1.NAME, TABLE_1.NOTE;

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0