selecting most recent row from joined table in MySQL

user_18676264 0
Viewed 36

I have two tables, Project and Projectnote

There is a one to many relationship between project and projectnote.

I want to be able to list my projects and select the most recent projectnotes based on the id.

Is this possible to do in Mysql query, I can't figure it out.

Thanks for any help!

Edit: so far I have a basic query (below) that joins the two tables. However, this only selects projects where a note exists and I get multiple rows where there are several notes per project.

SELECT `driver_checkins`.*, `driver_trips`.`id` AS `trip_id`, `driver_trips`.`trip_num` AS `trip_num`, `driver_trips`.`status` AS `trip_status`, `driver_trips`.`ride_date` AS `ride_date`, `driver_trips`.`today_date` AS `trip_today_date`, `driver_trips`.`pick_up_time` AS `pick_up_time`, `driver_trips`.`d_time` AS `d_time`, `driver_trips`.`trip_type` AS `trip_type`
FROM `driver_checkins`
LEFT JOIN `driver_trips` ON `driver_trips`.`driver_id` = `driver_checkins`.`driver_id` WHERE `checkin_status` = 1 AND `booking_status` = 0;
mysql sql
1 Answers

Using a GROUP BY clause and the GROUP_CONCAT function should do the trick.

I am assuming that "driver_checkins" is your "Project" table and "driver_trips" is your "Projectnote" table.

SELECT `driver_checkins`.*, GROUP_CONCAT(`driver_trips`.`id`, `driver_trips`.`status` ORDER BY `driver_trips`.id DESC SEPARATOR " --- " LIMIT 3)
FROM `driver_checkins`
LEFT JOIN `driver_trips` ON `driver_trips`.`driver_id` = `driver_checkins`.`driver_id`
WHERE `checkin_status` = 1 AND `booking_status` = 0
GROUP BY `driver_checkins`.id;

This should display id and status for the last 3 driver_trips per driver_checkin, separated by " --- ".

Something to consider: while in many cases ordering by id will work chronologically, it's always better to add a timestamp column (e.g. called created) instead to order by chronologically.

user_88745450
Related