如何获得每一个理由ID在我的表中？

Question

Hello,

Can anyone help me out with my query, I want to have every last reasonID of each student in the table.

i tried hours, but i could not find the solution.

this is my query Laravel Query:

    DB::table('studentsallstatuses as sas')
    ->whereIn('sasr.reason', $req->studentStatuses)
    ->where('sas.semester', $req->semesters)
    ->join('studentsallstatusesreasons as sasr', 'sasr.parentID', '=', 'sas.studentStatusID')                        
    ->select('sas.studentID', 'sasr.parentID','sasr.reason', 'sasr.reasonID')
    ->orderBy('sas.studentID', 'DESC')->get();

and this is my MySql Query:

    select `sas`.`studentID` , `sasr`.`parentID`, `sasr`.`reason`, `sasr`.`reasonID` 
    from `studentsallstatuses` as `sas` 
    inner join `studentsallstatusesreasons` as `sasr` on `sasr`.`parentID` =`sas`.`studentStatusID` 
    where `sasr`.`reason` in (50,121) and  `sas`.`semester` = 11 ORDER BY `sas`.`studentID`;

and the outcome is:

studentID  parentID     reason  reasonID    
444        369          50      160 
444        369          50      161 
444        369          50      162 
444        369          50      163 
555        376          121     177 
555        376          121     178 
555        376          121     188 
555        376          121     190 
555        376          121     191 
555        376          121     166 
555        376          121     176 
666        377          121     179 
666        377          121     189 
666        377          121     192 
666        377          121     167

table sturcture

Answer 1

Apply left join with compare condition and to fetch last record for each student,

SELECT `sas`.`studentID` , `sasr`.`parentID`, `sasr`.`reason`, `sasr`.`reasonID` 
FROM `studentsallstatusesreasons` as `sasr` 
INNER JOIN `studentsallstatuses` as `sas` on `sasr`.`parentID` =`sas`.`studentStatusID` 
LEFT JOIN studentsallstatusesreasons sasr1 ON (sasr1.parentID = sasr.parentID AND sasr.reasonID < sasr1.reasonID)
WHERE sasr1.reasonID IS NULL AND `sasr`.`reason` in (50,121) and  `sas`.`semester` = 11
ORDER BY `sas`.`studentID`;