Retrieve Values From Child Table

Hello there,





I need to get the last value (status) from a child table. I try to simplify the problem with the following example.





Create Table Users


(


UserId int,


Lastname nvarchar(50)


)





Create Table UserStatus


(


UserId int,


Date datetime,


StatusId int


)





Create Table Status


(


StatusId int


Status nvarchar(50)


)





A user will go through all Status one by one. (1) Registered -> (2) In progress -> (3) authorized.


Now I want to know which users are in progress (2) but a simple select statement like:





Select LastName from Users Inner Join Users.UsersId = UserStatus.UserId Where UsersStatus.StatusId = 2





Will not return the wanted records because all authorized Users have been in this status.





I hope you understand the problem and can help me out.





Thx in advance.





Etinuz