Select Join with last Mysql record

Question

Good morning,

I’m having trouble doing this search in mysql:

I would like to get all active users with their last trips (last registration):

Command used:

SELECT u.id, t.user_id, u.name, u.status, t.name as last_trip
FROM users u
LEFT JOIN trips t ON u.id = t.user_id AND t.id = ( SELECT MAX(*) FROM trips)
WHERE u.status = 'active';

Answer 1

     SELECT u.id AS `user_id`, u.`name`, u.`status`,
       (SELECT t.`name` FROM trips t WHERE t.user_id = u.id ORDER BY t.id DESC LIMIT 1) AS `last_trip`
     FROM users u
     WHERE u.`status` = "active"

This is probably not the perfect solution, but it already solves your problem.