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.

CodePudding user response：

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;