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

idleave
6752011-05-04
6752012-04-15
6752021-06-23
3452019-10-14
3452011-04-05
3452019-02-13

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

nameidnotelast absent
John Doe675good student2011-05-04
John Doe675good student2012-04-15
John Doe675good student2021-06-23
Jack Sparrow345average student2019-10-14
Jack Sparrow345average student2019-10-14
Jack Sparrow345average student2019-02-13
Ryan Reynolds567below average student2011-07-22

Here is my expected output.

nameidnotelast absent
John Doe675good student2021-06-23
Jack Sparrow345average student2019-10-14
Ryan Reynolds567below average student2011-07-22

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

0 0 votes
Article Rating
Subscribe
Notify of
guest

0 Comments
Inline Feedbacks
View all comments
0
Would love your thoughts, please comment.x
()
x