>>>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!
>>
>>Mike,
>>I am not sure I understand what you want.
>>You just want to update records in StatusHistory with new date and status, you want to insert new records in that table with new date and status or you want to update member's info for these which are already in StatusHistory and Add new records for these which are not there. Can you post some data and desired result?
>
>Hi Borislav
>
>I don't want to update the statushistory. That will be done in a screen specifically for that purpose. I want to produce a listing of each person with their current status and when it changed.
>
>
member
>id name
>1 mike
>
>statushistory
>id status effdate
>1 I 01/01/2005
>1 A 01/01/2007
>
>result
>id name status effdate
>i mike A 01/01/2007
I prefer that way:
CREATE CURSOR Members (MemberId int, Name C(20))
CREATE CURSOR StatusHistory (MemberId int, ChangeDate D, Status C(1))
INSERT INTO Members VALUES (1, [mike])
INSERT INTO StatusHistory VALUES (1, DATE(2005,1,1), [I])
INSERT INTO StatusHistory VALUES (1, DATE(2007,1,1), [A])
SELECT * ;
FROM Members mb ;
INNER JOIN;
(SELECT ff.* FROM StatusHistory ff;
INNER JOIN (SELECT MemberId, MAX(ChangeDate) AS ChangeDate;
FROM StatusHistory ttt;
GROUP BY MemberId) ss;
ON ff.MemberId = ss.MemberId AND;
ff.ChangeDate = ss.ChangeDate) sh;
ON Mb.MemberId =sh.MemberId
Sergey's suggestion would works also (Hmmm, did you have any doubt about it :o))
Against Stupidity the Gods themselves Contend in Vain - Johann Christoph Friedrich von Schiller
The only thing normal about database guys is their tables.