Conditional SQL Query?

Iin SQL Server 2000 I have two tables that I need to join table
A and table B. The result set is a little tricky though. Table A has a set
of columns that are duplicated in table B. The reason is if there is no
data in these columns in table A, then that means that the data "defaults"
to the same named columns in table B. There is a many-to-one relationship
from A to B. What I would like to do is build this join query such that I
would return X number of columns using alias column names. I would like the
query to be able to populate those alias columns with the column values from
table A if there is data in those columns, but if there is no data then
populate those alias columns with the column values from table B. So in
essence, I have something like this:
Table A
ID
B_ID
A_1
A_2
Table B
ID
B_1
B_2
I'd like to build a query that joins these tables on (A.B_ID = B.ID), and
return these alias columns:
COL_1: This retuns data from A_1 if data exists in this column, otherwise
returns data from B_1.
COL_2: This retuns data from A_2 if data exists in this column, otherwise
returns data from B_2.
Any help would be much appreciated.
Thanks!Try:
select
isnull (A_1, B_1)
, isnull (A2, B_2)
from
A
left join
B on B.ID = A.ID
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
.
"epigram" <nospam@.spammy.com> wrote in message
news:1112011902. 9f7c78e9def104fa4579b49849e7cce5@.bubbane
ws...
Iin SQL Server 2000 I have two tables that I need to join table
A and table B. The result set is a little tricky though. Table A has a set
of columns that are duplicated in table B. The reason is if there is no
data in these columns in table A, then that means that the data "defaults"
to the same named columns in table B. There is a many-to-one relationship
from A to B. What I would like to do is build this join query such that I
would return X number of columns using alias column names. I would like the
query to be able to populate those alias columns with the column values from
table A if there is data in those columns, but if there is no data then
populate those alias columns with the column values from table B. So in
essence, I have something like this:
Table A
ID
B_ID
A_1
A_2
Table B
ID
B_1
B_2
I'd like to build a query that joins these tables on (A.B_ID = B.ID), and
return these alias columns:
COL_1: This retuns data from A_1 if data exists in this column, otherwise
returns data from B_1.
COL_2: This retuns data from A_2 if data exists in this column, otherwise
returns data from B_2.
Any help would be much appreciated.
Thanks!|||Hello, epigram!
You wrote on Mon, 28 Mar 2005 07:28:22 -0500:
e> I'd like to build a query that joins these tables on (A.B_ID = B.ID),
e> and return these alias columns:
e> COL_1: This retuns data from A_1 if data exists in this column,
e> otherwise returns data from B_1.
e> COL_2: This retuns data from A_2 if data exists in this column,
e> otherwise returns data from B_2.
SELECT B.ID,
COALESCE(A_1, B_1) as COL_1,
COALESCE(A_2, B_2) as COL_2,
FROM A JOIN B ON A.B_ID = B.ID
e> Any help would be much appreciated.
e> Thanks!
With best regards, Alexander Sinitsin. E-mail: al_sin[dog]ukr.net|||Did you read your last post?
http://support.microsoft.com/newsgr...n-us&sloc=en-us
AMB
"epigram" wrote:

> Iin SQL Server 2000 I have two tables that I need to join table
> A and table B. The result set is a little tricky though. Table A has a s
et
> of columns that are duplicated in table B. The reason is if there is no
> data in these columns in table A, then that means that the data "defaults"
> to the same named columns in table B. There is a many-to-one relationship
> from A to B. What I would like to do is build this join query such that I
> would return X number of columns using alias column names. I would like t
he
> query to be able to populate those alias columns with the column values fr
om
> table A if there is data in those columns, but if there is no data then
> populate those alias columns with the column values from table B. So in
> essence, I have something like this:
> Table A
> ID
> B_ID
> A_1
> A_2
> Table B
> ID
> B_1
> B_2
> I'd like to build a query that joins these tables on (A.B_ID = B.ID), and
> return these alias columns:
> COL_1: This retuns data from A_1 if data exists in this column, otherwise
> returns data from B_1.
> COL_2: This retuns data from A_2 if data exists in this column, otherwise
> returns data from B_2.
> Any help would be much appreciated.
> Thanks!
>
>|||I couldn't. For some reason, my newsreader program was telling me that the
responses to that original post were unavailabe.
Thanks.
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:90602A4C-1EB2-4693-BC4C-79D35C8FA263@.microsoft.com...
> Did you read your last post?
> [url]http://support.microsoft.com/newsgroups/default.aspx?dg=microsoft.public.sqlserv
er.programming&mid=91835d68-563b-4260-a3ae-83dfdd2e8b2e&sloc=en-us&sloc=en-us[/url
]
>
> AMB
>
> "epigram" wrote:
>