Re: Which column is taken in JOIN with multiple columns and different content ?

Tobias Merler schrieb:
Assume I join two tables.
Both of these tables contain a column with name "col1". This column ist not he primary key.
However this column contains different values which value is taken ?

Lets see the following example:

Select *
FROM tablea a, tableb b
Where a.id = b.id

tablea:

id col1 col2

1 aaa ddd
2 bbb eee
3 ccc fff


tableb:

id col1 col3

1 zzz uuu
2 xxx vvv
3 yyy www

The result table will look like:

id col1 col2 col3

1 ??? ddd uuu
2 ??? eee vvv
3 ??? fff www

Which values will be for the questions marks ?

Ok, I know the data model is not perfect and the two tables could be merged.
But keep in mind that the sample is only a simplified version of a more complex SQL statement. So lets concentrate on my question.

Thank you

Toby




You will get both values: The result table will look like this one

id col1 col2 id_1 col1_1 col3
1 aaa ddd 1 zzz uuu
2 bbb eee 2 xxx vvv
3 ccc fff 3 yyy www


Now let us asume that you run the following statement:

select col1, col2, col3
from tablea, tableb
where tablea.id = tableb.id

Then you will receive an error message because oracle does not know which col1 to use. In this case you have to correct the statement:

select tablea.col1, col2, col3
from tablea, tableb
where tablea.id = tableb.id

Therefore it is a good idea, to use tablenames in your select clause:

select tablea.col1, tablea.col2, tableb.col3
from tablea, tableb
where tablea.id = tableb.id


Hope this helps

Martin

--
Firma/Company: CRESD GmbH
Phone: +49-89-65 30 95 63 Fax: +49-89-65 30 95 64
WWW: http://www.cresd.de
S-Mail: Freibadstr. 14, D-81543 Muenchen

Geschaeftsfuehrer: Christina Ragg, Martin Gaeckler
Steuernr.: 143/127/10030 USt. ID: DE218181906
Handelsregisterblatt: Muenchen 137833
Sitz der Geselschaft: Muenchen

PGP-Key: http://www.cresd.de/edv/pgpkey.txt
Open BC (Einladung) http://www.openbc.com/go/invita/4561755
.

Relevant Pages