Left join to get the 1 most recent

Firstly table structure - 1 user has multiple status records. When a new status record is created it gets the current datetime inserted into 'date_created'.

user
user_id
site_id

status
status
notes
date_created


I wish to display a list of all users and show beside them their current/most recent status record. Here is my code currently:


SELECT user.*, status.date_created, status.status, status.notes,
DATE_FORMAT(status.date_created,'%l:%i %p') as last_updated_time,
DATE_FORMAT(status.date_created,'%e/%c') as last_updated_date
FROM user
LEFT JOIN status ON status.user_id = user.user_id
WHERE user.site_id = 1
GROUP BY user.user_id
ORDER BY
CASE user.user_id WHEN 8 THEN 0 ELSE user.user_id END


Currently via the LEFT JOIN and GROUP this does display a list of all users with 1 status record per user but it's simply the first status record in the table (ordering by id i guess), not the most recent.

How can I get it to display the most recent instead, based on date_created. I've tried adding ordering to the left join but this returned an error.


:confused:

Your group by clause is incorrect, see GROUP BY HIDDEN FIELDS in the manual to see why.

as for your question, if you are trying to match something don't use an outer join, that returns unmatched as well as matched rows so use an inner join.

and the answer you would most likely use is a co-related subquery something along the lines of:


select
user.user
user.user_id
user.site_id

status.status
status.notes
status.date_created
from user inner join status
on user.user_id = status.status_id
where
status.date_created =
(select max(dt.date_created) from yourtable as dt
where dt.user_id = user.user_id)

Thanks for the reply, I tried out your example and further queries using subquery method but all failed. I've just discovered the mysql version we are using is 4.0 so I won't be able to use these.

well you should upgrade if you have the ability to do so. if you are paying a host then you should find another. mysql 4.0.x was replaced in October 2004.