Posted by Roy Harvey (SQL Server MVP) on 10/31/07 20:39

SELECT A.FullName, A.UserName, B.Status, B.DateChanged
FROM Person as A
JOIN Status as B
ON A.UserName = B.UserName
WHERE B.DateChanged =
(SELECT MAX(C.DateChanged)
FROM Status as C
WHERE A.UserName = C.UserName)

This is called a correlated subquery since it references a column from
the outer query.

Roy Harvey
Beacon Falls, CT

On Wed, 31 Oct 2007 13:08:13 -0700, 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!

[Back to original message]