469,076 Members | 1,402 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,076 developers. It's quick & easy.

Help with SQL query?

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!

Oct 31 '07 #1
3 1518
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
Oct 31 '07 #2
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!
Oct 31 '07 #3
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. :)

Nov 1 '07 #4

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

11 posts views Thread by James | last post: by
9 posts views Thread by netpurpose | last post: by
9 posts views Thread by Dom Boyce | last post: by
5 posts views Thread by Steve Patrick | last post: by
6 posts views Thread by Takeadoe | last post: by
47 posts views Thread by Jo | last post: by
1 post views Thread by CARIGAR | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.