how to get every last reasonID In my Table?

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

[This is similar to your query](https://stackoverflow.com/questions/1313120/retrieving-the-last-record-in-each-group-mysql) — Shashank Singh, Jul 04 '19 at 07:06
Do you have models and relationships set up for the two tables? — Rwd, Jul 04 '19 at 07:58

Rahul · Accepted Answer · 2019-07-04T07:42:08.497

0

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`;

edited Jul 04 '19 at 07:42

answered Jul 04 '19 at 07:00

Rahul

18,271
7
41
60

hello @Drakula Predator م thanks for the answer. but `sas1.reasonID` is not in `studentsallstatuses` it is in `studentsallstatusesreasons`. it give unknown column `sas1.reasonID` – Ali Reza Jul 04 '19 at 07:16
You too ain't shared your table structure of table. Quite blank to judge :| Please share your table structure. – Rahul Jul 04 '19 at 07:17

how to get every last reasonID In my Table?

1 Answers1