469,348 Members | 1,266 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

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

OUTER JOIN with multiple tables and a plus sign?

I am trying to select specific columns from multiple tables based on a
common identifier found in each table.

For example, the three tables:

PUBACC_AC
PUBACC_AM
PUBACC_AN

each have a common column:

PUBACC_AC.unique_system_identifier
PUBACC_AM.unique_system_identifier
PUBACC_AN.unique_system_identifier
What I am trying to select, for example:

PUBACC_AC.name
PUBACC_AM.phone_number
PUBACC_AN.zip

where the TABLE.unique_system_identifier is common.

For example:

----------------------------------------------
PUBACC_AC
=========
unique_system_identifier name
1234 JONES

----------------------------------------------
PUBACC_AM
=========
unique_system_identifier phone_number
1234 555-1212

----------------------------------------------
PUBACC_AN
=========
unique_system_identifier zip
1234 90210
When I run my query, I would like to see the following returned as one
blob, rather than the separate tables:

-------------------------------------------------------------------
unique_system_identifier name phone_number zip
1234 JONES 555-1212 90210
-------------------------------------------------------------------
I think this is an OUTER JOIN? I see examples on the net using a plus
sign, with mention of Oracle. I'm not running Oracle...I am using
Microsoft SQL Server 2000.

Help, please?

P. S. Will this work with several tables? I actually have about 15
tables in this mess, but I tried to keep it simple (!??!) for the above
example.

Thanks in advance for your help!

NOTE: TO REPLY VIA E-MAIL, PLEASE REMOVE THE "DELETE_THIS" FROM MY E-MAIL
ADDRESS.

Who actually BUYS the cr@p that the spammers advertise, anyhow???!!!
(Rhetorical question only.)
Jul 20 '05 #1
1 16261
You do the following:

SELECT PUBACC_AC.Name + PUBACC_AM.Phone_number + PUBACC_AN.zip
FROM PUBACC_AC
INNER JOIN PUBACC_AM
ON PUBACC_AM.unique_col = PUBACC_AC.unique_col
INNER JOIN PUBACC_AN
ON PUBACC_AN.unique_col = PUBACC_AC.unique_col

That's it - you do inner join, if you want only those records, for which
unique_col value exists in all 3 tables.
Or, if you replace "INNER JOIN" with "FULL JOIN", which is the same as OUTER
JOIN in Oracle, you will get
as meny records as number of unique_col values.

Thats it!

Hope it helped,
Andrey aka Muzzy
"TeleTech1212" <te**********************@yahoo.com> wrote in message
news:Xn**********************************@207.115. 63.158...
I am trying to select specific columns from multiple tables based on a
common identifier found in each table.

For example, the three tables:

PUBACC_AC
PUBACC_AM
PUBACC_AN

each have a common column:

PUBACC_AC.unique_system_identifier
PUBACC_AM.unique_system_identifier
PUBACC_AN.unique_system_identifier
What I am trying to select, for example:

PUBACC_AC.name
PUBACC_AM.phone_number
PUBACC_AN.zip

where the TABLE.unique_system_identifier is common.

For example:

----------------------------------------------
PUBACC_AC
=========
unique_system_identifier name
1234 JONES

----------------------------------------------
PUBACC_AM
=========
unique_system_identifier phone_number
1234 555-1212

----------------------------------------------
PUBACC_AN
=========
unique_system_identifier zip
1234 90210
When I run my query, I would like to see the following returned as one
blob, rather than the separate tables:

-------------------------------------------------------------------
unique_system_identifier name phone_number zip
1234 JONES 555-1212 90210
-------------------------------------------------------------------
I think this is an OUTER JOIN? I see examples on the net using a plus
sign, with mention of Oracle. I'm not running Oracle...I am using
Microsoft SQL Server 2000.

Help, please?

P. S. Will this work with several tables? I actually have about 15
tables in this mess, but I tried to keep it simple (!??!) for the above
example.

Thanks in advance for your help!

NOTE: TO REPLY VIA E-MAIL, PLEASE REMOVE THE "DELETE_THIS" FROM MY E-MAIL
ADDRESS.

Who actually BUYS the cr@p that the spammers advertise, anyhow???!!!
(Rhetorical question only.)

Jul 20 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

1 post views Thread by Dave | last post: by
8 posts views Thread by Matt | last post: by
3 posts views Thread by Martin | last post: by
4 posts views Thread by Anthony Robinson | last post: by
2 posts views Thread by dskillingstad | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by suresh191 | last post: by
1 post views Thread by Marylou17 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.