There are 3 tables, namely:
Employees:
| ID | Name | Gender |
|---|---|---|
| 1 | abc | male |
| 2 | def | female |
| 3 | ghi | male |
| 4 | mno | male |
| 5 | rms | female |
employee_log: it's a table which will have the log entry for entries/modifications made in the employees table with the admin_id of the admin who last performed an action on that employee in the employees table.
| ID | Parent_ID | admin_id | last_modified_time |
|---|---|---|---|
| 1 | 3 | 1 | some timestamp |
| 2 | 3 | 2 | some timestamp |
| 3 | 4 | 2 | some timestamp |
| 4 | 5 | 3 | some timestamp |
| 5 | 4 | 3 | some timestamp |
| 6 | 3 | 1 | some timestamp |
| 7 | 5 | 2 | some timestamp |
| 8 | 5 | 1 | some timestamp |
Admin:
| ID | |
|---|---|
| 1 | some-email1 |
| 2 | some-email2 |
| 3 | some-email3 |
I want to write a query such that I get each of the rows in employees table with the admin email who last modified it based on the max id corresponding to every parent id.
Note parent id in employee_log is a foreign key to id field in employees table.
Note: There can be entries in the employees table which may not have any log entry in the employee_log table. These are basically entries made before the employee_log table was introduced. In this case such employees also need to be displayed, however the admin who last modified the employee will be shown as null.
Could someone please help with this? Any help would be appreciated.
