How to get info in SELECT directly instead of UPDATE? Thanks.

[rong.guo]

Greetings!

I have the 3 raw data tables below, and would like to extract some
data. My current query is really cumbersome, does anyone know a better
way to get all the info (attach the right state to the lowest zip code)
in one step? Thanks a lot! Here are the details.

- Ideal Output, for each person , keep
(1) person_id (From Table A)
(2) The earliest open_date of accounts starting with 2 (From Table A)
(3) ssn (From Table B)
(4) Zip and state info (From Table C), but only keep the lowest zip and
state.

-Table A -
person_id account open_date
10001 22000001 5/15/2003
10001 22000002 6/20/2004
10001 30000001 2/2/2002
10002 22000003 8/12/2004
10002 22000004 9/15/2004
10002 30000002 2/16/2005

-Table B -
person_id zip state
10001 11111 AA
10001 22222 CC
10002 33333 BB

--Table C -
person_id ssn
10001 1234567
10002 2345678

-Ideal Output-
person_id min_open ssn zip state
10001 5/15/2003 1234567 11111 AA
10002 8/12/2004 2345678 33333 BB

Here is what I did:
Select a.person_id, min(a.open_date), b.ssn, min(c.zip) as zip
,cast (0 as varchar) as state
Into output

From TableA as a Join TableB as b
on a.person_id=b.person_id
Join TableC as c
on a.person_id=c.person_id
Where a.account like '2%'
Group by a.person_id, b.ssn
Order by a.person_id

Update output
Set output.state=b.stateFrom output as a

Join TableC as b
on a.person_id=b.person_id
and a.zip=b.zip

[Erland Sommarskog]

(ro******@gmail.com) writes:

I have the 3 raw data tables below, and would like to extract some
data. My current query is really cumbersome, does anyone know a better
way to get all the info (attach the right state to the lowest zip code)
in one step? Thanks a lot! Here are the details.

- Ideal Output, for each person , keep
(1) person_id (From Table A)
(2) The earliest open_date of accounts starting with 2 (From Table A)
(3) ssn (From Table B)
(4) Zip and state info (From Table C), but only keep the lowest zip and
state.

For these kind of these questions, it's always recommendable to post:

o CREATE TABLE statements for your tables.
o INSERT statements with sample data.
o The desired result given the sample.

This makes it possible to easily copy and paste into Query Analyzer to
develop a tested query. Since your sample data was not in this form,
this query is untested:

SELECT a1.person_id, a1.min_open, c.ssn, a1.min_zip, b.state
FROM (SELECT a.person_id, min_open = MIN(a.open_date),
min_zip = MIN(b.zip)
FROM table_a a
JOIN table_b b ON a.person_id = b_person_id
GROUP BY a.person_id) AS a1
JOIN table_b b ON a1.person_id = b.person_id
AND a1.min_zip = b.zip
JOIN table_c c ON a1.person_id = c.person_id

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[rong.guo]

Thanks, Erland.

Because I am not familiar with CREATE and INSERT, I didn't use them
this time. Sorry for the inconvenience. I will certainly try it next
time.

Thanks again!