Complex One to Many Query in MySql

I have searched but can't condense the question enough to find a relevant answer. My experience tells me this may be a design issue...

current_status
id
user_id
status_id
created

users
user_id
other...

current_status is used mostly for auditing purposes. Every time a user status changes, a new开发者_C百科 record is inserted in current_status. Some status examples are 'working', 'fired', 'applied', 'on layoff', etc.

The query - I want to return just the most recent status of each user plus the user details...

SELECT users.*, current_status.status_id AS status 
FROM users 
JOIN current_status ON users.user_id = current_status.user_id 
WHERE users.user_id = '1'

How do I tell the query to return the most recent status_id for the user?

ORDER BY current_status.created DESC LIMIT 0,1

However, is that truly the accurate way to do it because what if I want to return multiple users?

Any help would be greatly appreciated.

---

EDIT Think I have the hang of your tables/columns now...

SELECT    users.*,
         (SELECT   current_status.status_id
          FROM     current_status
          WHERE    current_status.user_id = users.user_id
          ORDER BY current_status.created DESC
          LIMIT    1) AS status
FROM      users
WHERE     users.user_id = '1'

EDITv2 Also, if you want to join the table to save yourself a query later on, you can use:

SELECT    users.*, current_status.*
FROM      users
  LEFT JOIN current_status
  ON        current_status.status_id = (
    SELECT   current_status.status_id
    FROM     current_status
    WHERE    current_status.user_id = users.user_id
    ORDER BY current_status.created DESC
    LIMIT    1
  )
WHERE     users.user_id = '1'

---

Perhaps something like this?

SELECT users.*, current_status.status_id AS status
FROM
users,
current_status
WHERE users.user_id = '1' AND
users.user_id = current_status.user_id AND
current_status.status_id IN (SELECT max(status_id) FROM current_status GROUP_BY user_id))

---

Sadly MySQL doesn;t support over(order by ...) feature. That's going to take a subquery to select all last statuses:

select 
  user.user_id, status.status_id, status.created
from user 
  join status on user.user_id = status.user_id
  join (
  select user_id, max(created)
  from status
  group by user_id
) last_status
on 
  status.user_id = last_statuses.user_id
    and status.created = last_statuses.created