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

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_idaccountopen_date
10001220000015/15/2003
10001220000026/20/2004
10001300000012/2/2002
10002220000038/12/2004
10002220000049/15/2004
10002300000022/16/2005

-Table B -
person_idzip state
1000111111AA
1000122222CC
1000233333BB

--Table C -
person_idssn
100011234567
100022345678

-Ideal Output-
person_idmin_openssnzipstate
100015/15/2003123456711111AA
100028/12/2004234567833333BB

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.state
>From output as a
Join TableC as b
on a.person_id=b.person_id
and a.zip=b.zip(rong.guo@.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, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||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!

How to get email to show in subscriptions INSTEAD of UserID

I've looked at most of the emails from people that are confused regarding how
to setup the email. But in the
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/rsadmin/htm/arp_configserver_v1_4bzl.asp
from Microsoft, it indicates that in SendEmailToUserAlias "true=The e-mail
address of the user creating the subscription is used. This is the default
value", but it is putting the User!UserID instead.
We are using Exchange, and it takes SMTP request OK using SendUsing 2. I
can send emails to those in the same domain, but I'm wondering how to get the
username to show their email address, instead of blanking it in the TO: box.
I'd like it to only go to the user that made the subscription only, but the
SMTP email isn't being recognized by Exchange as a valid user name (it isn't
getting resolved).
If I typed in a username in an Exchange window it WILL resolve, but through
Subscriptions it will not.
Here are some of the important settings:
<SMTPServerPort>25</SMTPServerPort>
<SMTPAccountName></SMTPAccountName>
<SMTPConnectionTimeout>60</SMTPConnectionTimeout>
<SMTPServerPickupDirectory></SMTPServerPickupDirectory>
<SMTPUseSSL>0</SMTPUseSSL>
<SendUsing>2</SendUsing>
<SMTPAuthenticate>2</SMTPAuthenticate>
<SendEmailToUserAlias>false</SendEmailToUserAlias>
<DefaultHostName>companyname.com</DefaultHostName>
<PermittedHosts></PermittedHosts>The subscription UI does not attempt to resolve the name with the Exchange
server. It merely takes the current authenticated user and places it in the
TO line. If your user names don't resolve to your exchange names then set
SendEmailToUserAlias and each user will have to type in their own address.
--
-Daniel
This posting is provided "AS IS" with no warranties, and confers no rights.
"David Bienstock" <DavidBienstock@.discussions.microsoft.com> wrote in
message news:8F5C45D3-4388-4F83-A58F-46467BB0C1B4@.microsoft.com...
> I've looked at most of the emails from people that are confused regarding
> how
> to setup the email. But in the
> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/rsadmin/htm/arp_configserver_v1_4bzl.asp
> from Microsoft, it indicates that in SendEmailToUserAlias "true=The e-mail
> address of the user creating the subscription is used. This is the default
> value", but it is putting the User!UserID instead.
> We are using Exchange, and it takes SMTP request OK using SendUsing 2. I
> can send emails to those in the same domain, but I'm wondering how to get
> the
> username to show their email address, instead of blanking it in the TO:
> box.
> I'd like it to only go to the user that made the subscription only, but
> the
> SMTP email isn't being recognized by Exchange as a valid user name (it
> isn't
> getting resolved).
> If I typed in a username in an Exchange window it WILL resolve, but
> through
> Subscriptions it will not.
> Here are some of the important settings:
> <SMTPServerPort>25</SMTPServerPort>
> <SMTPAccountName></SMTPAccountName>
> <SMTPConnectionTimeout>60</SMTPConnectionTimeout>
> <SMTPServerPickupDirectory></SMTPServerPickupDirectory>
> <SMTPUseSSL>0</SMTPUseSSL>
> <SendUsing>2</SendUsing>
> <SMTPAuthenticate>2</SMTPAuthenticate>
> <SendEmailToUserAlias>false</SendEmailToUserAlias>
> <DefaultHostName>companyname.com</DefaultHostName>
> <PermittedHosts></PermittedHosts>|||The BOL says "true=The e-mail address of the user creating the subscription
is used. This is the default value", but it is putting the User!UserID
instead.
My point was I wanted it the way it said but what it said wasn't exactly
right.
"Daniel Reib (MSFT)" wrote:
> The subscription UI does not attempt to resolve the name with the Exchange
> server. It merely takes the current authenticated user and places it in the
> TO line. If your user names don't resolve to your exchange names then set
> SendEmailToUserAlias and each user will have to type in their own address.
> --
> -Daniel
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "David Bienstock" <DavidBienstock@.discussions.microsoft.com> wrote in
> message news:8F5C45D3-4388-4F83-A58F-46467BB0C1B4@.microsoft.com...
> > I've looked at most of the emails from people that are confused regarding
> > how
> > to setup the email. But in the
> > http://msdn.microsoft.com/library/default.asp?url=/library/en-us/rsadmin/htm/arp_configserver_v1_4bzl.asp
> > from Microsoft, it indicates that in SendEmailToUserAlias "true=The e-mail
> > address of the user creating the subscription is used. This is the default
> > value", but it is putting the User!UserID instead.
> >
> > We are using Exchange, and it takes SMTP request OK using SendUsing 2. I
> > can send emails to those in the same domain, but I'm wondering how to get
> > the
> > username to show their email address, instead of blanking it in the TO:
> > box.
> > I'd like it to only go to the user that made the subscription only, but
> > the
> > SMTP email isn't being recognized by Exchange as a valid user name (it
> > isn't
> > getting resolved).
> >
> > If I typed in a username in an Exchange window it WILL resolve, but
> > through
> > Subscriptions it will not.
> >
> > Here are some of the important settings:
> >
> > <SMTPServerPort>25</SMTPServerPort>
> > <SMTPAccountName></SMTPAccountName>
> > <SMTPConnectionTimeout>60</SMTPConnectionTimeout>
> > <SMTPServerPickupDirectory></SMTPServerPickupDirectory>
> > <SMTPUseSSL>0</SMTPUseSSL>
> > <SendUsing>2</SendUsing>
> > <SMTPAuthenticate>2</SMTPAuthenticate>
> >
> > <SendEmailToUserAlias>false</SendEmailToUserAlias>
> > <DefaultHostName>companyname.com</DefaultHostName>
> > <PermittedHosts></PermittedHosts>
>
>|||The user creating the service is the authenticated user, which is also the
User!UserID. I am not sure why you think they would be different.
--
-Daniel
This posting is provided "AS IS" with no warranties, and confers no rights.
"David Bienstock" <DavidBienstock@.discussions.microsoft.com> wrote in
message news:17149E65-0C81-4579-9D1F-E24EF95E89AF@.microsoft.com...
> The BOL says "true=The e-mail address of the user creating the
> subscription
> is used. This is the default value", but it is putting the User!UserID
> instead.
> My point was I wanted it the way it said but what it said wasn't exactly
> right.
>
> "Daniel Reib (MSFT)" wrote:
>> The subscription UI does not attempt to resolve the name with the
>> Exchange
>> server. It merely takes the current authenticated user and places it in
>> the
>> TO line. If your user names don't resolve to your exchange names then
>> set
>> SendEmailToUserAlias and each user will have to type in their own
>> address.
>> --
>> -Daniel
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>>
>> "David Bienstock" <DavidBienstock@.discussions.microsoft.com> wrote in
>> message news:8F5C45D3-4388-4F83-A58F-46467BB0C1B4@.microsoft.com...
>> > I've looked at most of the emails from people that are confused
>> > regarding
>> > how
>> > to setup the email. But in the
>> > http://msdn.microsoft.com/library/default.asp?url=/library/en-us/rsadmin/htm/arp_configserver_v1_4bzl.asp
>> > from Microsoft, it indicates that in SendEmailToUserAlias "true=The
>> > e-mail
>> > address of the user creating the subscription is used. This is the
>> > default
>> > value", but it is putting the User!UserID instead.
>> >
>> > We are using Exchange, and it takes SMTP request OK using SendUsing 2.
>> > I
>> > can send emails to those in the same domain, but I'm wondering how to
>> > get
>> > the
>> > username to show their email address, instead of blanking it in the TO:
>> > box.
>> > I'd like it to only go to the user that made the subscription only, but
>> > the
>> > SMTP email isn't being recognized by Exchange as a valid user name (it
>> > isn't
>> > getting resolved).
>> >
>> > If I typed in a username in an Exchange window it WILL resolve, but
>> > through
>> > Subscriptions it will not.
>> >
>> > Here are some of the important settings:
>> >
>> > <SMTPServerPort>25</SMTPServerPort>
>> > <SMTPAccountName></SMTPAccountName>
>> > <SMTPConnectionTimeout>60</SMTPConnectionTimeout>
>> > <SMTPServerPickupDirectory></SMTPServerPickupDirectory>
>> > <SMTPUseSSL>0</SMTPUseSSL>
>> > <SendUsing>2</SendUsing>
>> > <SMTPAuthenticate>2</SMTPAuthenticate>
>> >
>> > <SendEmailToUserAlias>false</SendEmailToUserAlias>
>> > <DefaultHostName>companyname.com</DefaultHostName>
>> > <PermittedHosts></PermittedHosts>
>>