Help with SQL query?

[melissa820]

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!

[Gert-Jan Strik]

me********@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

[Roy Harvey (SQL Server MVP)]

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, me********@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!

[melissa820]

Thank you everyone for your help.

And no, it wasn't homework... just something I was asked to do at work
but which I'm not very familiar with. :)