>>Hi all
>>
>>This kind of query should be common yet I often seem to break it.
>>
>>Two tables
>>
>>Members, StatusHistory.
>>
>>Status history has the member id the status and the date of the change.
>>
>>I want to grab member info and the current status and change date as of this moment for each member.
>>
>>Please help.
>>
>>Thanks!
>
>Do you mean the maximum date in StatusHistory? If yes, there are several ways:
>

>select * from Members ;
>inner join (select Id, Max(date) from StatusHistory group by ID) as CurrentStatus ;
>on Members.ID = CurrentStatus.ID

>
>Let me write others after the break.

Slight correction for the first:

select Members.*, CurrentStatus.CurrentDate from Members ;
inner join (select Id, Max(ChangeDate) as CurrentDate from StatusHistory group by ID) as CurrentStatus ;
on Members.ID = CurrentStatus.ID

But it would not return you the current status from the StatusHistory, so you need to modify it even further, e.g. join with itself (StatusHistory) to get status corresponding to the Current Date.

Looks like Sergey's SQL will do the job better.