Kirk Strobeck Kirk Strobeck - 5 months ago 13
MySQL Question

LEFT JOIN of only the latest row from a many-to-one

Been banging my head against the wall and cannot solve this :\

SELECT
`people`.*,
`students`.*,
`student_class_relationships`.*,
`geo_checkin_on_campus`.`datetime_created` as checkin_time
FROM `student_class_relationships`
LEFT OUTER JOIN `students`
ON `student_class_relationships`.`student` = `students`.`id`
LEFT OUTER JOIN `people`
ON `students`.`student` = `people`.`id`
LEFT OUTER JOIN `geo_checkin_on_campus`
ON `students`.`id` = (
SELECT MIN(`geo_checkin_on_campus`.`student`)
FROM `geo_checkin_on_campus`
WHERE `geo_checkin_on_campus`.`student` = `students`.`id`
)
WHERE `class` = 56


The expected result is many rows that have only one entry per
students.id
.

Here is my schema

Answer

It is not the best query from performance perspective,

but just to fix your query here is my attempt:

SELECT
  `people`.*,
  `students`.*,
  `student_class_relationships`.*,
  geoCheckinOnCampus.datetimeCreated as checkin_time
FROM `student_class_relationships`
  LEFT JOIN `students`
    ON `student_class_relationships`.`student` = `students`.`id`
  LEFT JOIN `people`
    ON `students`.`student` = `people`.`id`
  LEFT JOIN 
    (
      SELECT 
        student,
        MAX(datetime_created) datetimeCreated
      FROM `geo_checkin_on_campus`
      GROUP BY `student`
    ) geoCheckinOnCampus
  ON `students`.`id` = geoCheckinOnCampus.`student`
 WHERE `class` = 56

Note According to @xQbert answer I would really change MIN to MAX function if you are looking for the latest datetime.

Comments