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