>

>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.

SELECT Mb.*, Sh.* ;  
  FROM Members mb ;  
  LEFT JOIN StatusHistory sh ON Mb.ID=sh.ID ;  
  WHERE Sh.ID IS NULL ;  
  	OR sh.ID IN ;  
  		(SELECT CTOBIN(RIGHT(MAX(DTOS(ChangeDate)+BINTOC(ID)),4)) ;  
  			FROM StatusHistory GROUP BY ID)

Idea by Igor Korolev from http://forum.foxclub.ru/read.php?32,177183,177232#msg-177232

This one I like the best.