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.uniqu e_system_identi fier
PUBACC_AM.uniqu e_system_identi fier
PUBACC_AN.uniqu e_system_identi fier
What I am trying to select, for example:
PUBACC_AC.name
PUBACC_AM.phone _number
PUBACC_AN.zip
where the TABLE.unique_sy stem_identifier is common.
For example:
----------------------------------------------
PUBACC_AC
=========
unique_system_i dentifier name
1234 JONES
----------------------------------------------
PUBACC_AM
=========
unique_system_i dentifier phone_number
1234 555-1212
----------------------------------------------
PUBACC_AN
=========
unique_system_i dentifier 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_i dentifier 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_THI S" FROM MY E-MAIL
ADDRESS.
Who actually BUYS the cr@p that the spammers advertise, anyhow???!!!
(Rhetorical question only.) 1 16654
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.uniqu e_col = PUBACC_AC.uniqu e_col
INNER JOIN PUBACC_AN
ON PUBACC_AN.uniqu e_col = PUBACC_AC.uniqu e_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
"TeleTech12 12" <te************ **********@yaho o.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.uniqu e_system_identi fier PUBACC_AM.uniqu e_system_identi fier PUBACC_AN.uniqu e_system_identi fier
What I am trying to select, for example:
PUBACC_AC.name PUBACC_AM.phone _number PUBACC_AN.zip
where the TABLE.unique_sy stem_identifier is common. For example:
---------------------------------------------- PUBACC_AC ========= unique_system_i dentifier name 1234 JONES
---------------------------------------------- PUBACC_AM ========= unique_system_i dentifier phone_number 1234 555-1212
---------------------------------------------- PUBACC_AN ========= unique_system_i dentifier 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_i dentifier 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_THI S" FROM MY E-MAIL ADDRESS.
Who actually BUYS the cr@p that the spammers advertise, anyhow???!!! (Rhetorical question only.) This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
by: Dave |
last post by:
Hi
I have the following 4 tables and I need to do a fully outerjoin on them.
create table A (a number, b number, c char(10), primary key (a,b))
create table B (a number, b number, c char(10), primary key (a,b))
create table C (a number, b number, c char(10), priamry key (a,b))
create table D (a number, b number, c char(10), priamry key (a,b))
In oracle 9i, the following query returns correct results set, if I were to
|
by: Matt |
last post by:
Hello
I have to tables ar and arb, ar holds articles and a swedish
description, arb holds descriptions in other languages.
I want to retreive all articles that match a criteria from ar and also
display their corresponding entries in arb, but if there is NO entry
in arb I still want it to show up as NULL or something, so that I can
get the attention that there IS no language associated with that
article.
|
by: thilbert |
last post by:
All,
I have a perplexing problem that I hope someone can help me with.
I have the following table struct:
Permission
-----------------
PermissionId
Permission
|
by: Dam |
last post by:
Using SqlServer :
Query 1 :
SELECT def.lID as IdDefinition,
TDC_AUneValeur.VALEURDERETOUR as ValeurDeRetour
FROM serveur.Data_tblDEFINITIONTABLEDECODES def,
serveur.Data_tblTABLEDECODEAUNEVALEUR TDC_AUneValeur
where def.TYPEDETABLEDECODES = 4
|
by: Steve |
last post by:
I have a SQL query I'm invoking via VB6 & ADO 2.8, that requires three
"Left Outer Joins" in order to return every transaction for a specific
set of criteria.
Using three "Left Outer Joins" slows the system down considerably.
I've tried creating a temp db, but I can't figure out how to execute
two select commands. (It throws the exception "The column prefix
'tempdb' does not match with a table name or alias name used in the
query.")
| |
by: Martin |
last post by:
Hello everybody,
I have the following question.
As a join clause on Oracle we use " table1.field1 = table2.field1 (+) "
On SQL Server we use " table1.field1 *= table2.field1 "
Does DB2 have the same type of operator, without using the OUTER JOIN
syntax ?
|
by: Anthony Robinson |
last post by:
I was actually just wondering if someone could possibly take a look
and tell me what I may be doing wrong in this query? I keep getting
ambiguous column errors and have no idea why...?
Thanks in advance!!!
SELECT AIM.AIMRETRIEVAL.AIMRETRIEVALID,
AIM.AIMRETRIEVAL.DESCRIPTION,
AIM.ARCHIVERETRIEVAL.ARCHIVERETRIEVALID,
AIM.ARCHIVERETRIEVAL.STATUSID,
|
by: dskillingstad |
last post by:
I would really appreciate someone's help on this, or at least point me
in the right direction....
I'm working on a permit database that contains 12 tables, and rather
than list all of the tables, I'll just list a few, as the links are the
same for all tables. These tables are:
tblPermitMain
tblApplicant
tblContractor
tblEngineer
|
by: Notgiven |
last post by:
I have three tables:
table1:
table2_ID
table3_ID
complete
table3:
table3_ID
name
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
| |
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
| |
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |