Погляди, правильно думаю хоть нет?)
Select
users.name, chat.text from users, chat, status_change
where
user.id = chat.user_id
And chat user_id = status_change.user_id
And status_change.status = 'active'
And
chat.date <
(Select date from status_change
where status = 'away'
Group by user_id
Having max(date) > (select max(date) from status_change where status not in 'away'))