Posted by Gert-Jan Strik on 10/31/07 20:39

melissa820@hotmail.com wrote:
>
> I have two tables: one with person information and the other with a
> person's status changes/updates. (A person's status can go from
> Prospective to Current to Completed.)
>
> Code:
> Person Table:
> UserName FullName
> jsmith Joe Smith
> bjones Bob Jones
> cblack Carol Black
>
> Status Table:
> UserName Status DateChanged
> jsmith Prospective 1/1/2006
> bjones Prospective 1/2/2006
> jsmith Current 3/3/2006
> cblack Prospective 3/3/2006
> bjones Current 4/4/2006
> bjones Completed 5/5/2006
>
> >From the Status Table it's obvious that "bjones" is Completed,
> "cblack" is Prospective, and "jsmith" is Current. But I'm not sure how
> to grab this info from these tables in a select. I'm thinking I might
> need the MAX(DateChanged) in there somewhere, but I have no clue how
> to use it.
>
> Eventually my goal is to say "show me all the names of the people who
> are Current".
>
> Any ideas? Thanks in advance, I am terrible at SQL queries!

I sure hope this is not homework...

Anyway, so here it goes (untested):

SELECT P.UserName
, P.FullName
, S.Status
, S.DateChanged
FROM Person P
INNER JOIN Status S
ON S.UserName = P.UserName
WHERE S.DateChanged = (
SELECT MAX(DateChanged)
FROM Status
WHERE UserName = P.UserName
)

--
Gert-Jan

[Back to original message]