Path: news.f.de.plusline.net!news-fra1.dfn.de!news-lei1.dfn.de!newsfeed.freenet.de!newspeer1.nwr.nac.net!border2.nntp.dca.giganews.com!nntp.giganews.com!novia!news-out.readnews.com!news-xxxfer.readnews.com!postnews.google.com!z35g2000cwz.googlegroups.com!not-for-mail
From: "William Robertson" <williamr2019@googlemail.com>
Newsgroups: comp.databases.oracle.misc
Subject: Re: Which column is taken in JOIN with multiple columns and different content ?
Date: 3 Mar 2007 03:52:11 -0800
Organization: http://groups.google.com
Lines: 68
Message-ID: <1172922731.798188.25310@z35g2000cwz.googlegroups.com>
References: <45e84bdf$0$23135$9b4e6d93@newsspool1.arcor-online.net>
   <Xns98E7F2012B91Bdigeratus2006@63.218.45.20>
NNTP-Posting-Host: 82.45.232.110
Mime-Version: 1.0
Content-Type: text/plain; charset="iso-8859-1"
X-Trace: posting.google.com 1172922740 7002 127.0.0.1 (3 Mar 2007 11:52:20 GMT)
X-Complaints-To: groups-abuse@google.com
NNTP-Posting-Date: Sat, 3 Mar 2007 11:52:20 +0000 (UTC)
In-Reply-To: <Xns98E7F2012B91Bdigeratus2006@63.218.45.20>
User-Agent: G2/1.0
X-HTTP-UserAgent: Mozilla/5.0 (Macintosh; U; Intel Mac OS X; en-US; rv:1.8.1.2) Gecko/20070219 Firefox/2.0.0.2,gzip(gfe),gzip(gfe)
Complaints-To: groups-abuse@google.com
Injection-Info: z35g2000cwz.googlegroups.com; posting-host=82.45.232.110;
   posting-account=14FQPQ0AAACEKI6mOeO_Or9AVEx3kAMJ
Xref: news.f.de.plusline.net comp.databases.oracle.misc:78428

On Mar 3, 4:38 am, Digeratus 2006 <digeratus2...@nospam.hotmaildotcom>
wrote:
> mer...@gmx.net (Tobias Merler) wrote innews:45e84bdf$0$23135$9b4e6d93@newsspool1.arcor-online.net:
>
>
>
> > 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
>
> Toby,
> Because of the conflicting column names, Oracle would give you a syntax
> error, try it.  The syntax in Oracle would be select a.*, b.*.  The
> resulting table should be apparent now.
>
> Andy Young

I'm nore puzzled about why anyone would think there might be a
problem. Would you also predict an error from this?

SQL> select deptno, dname, dname from dept;

Perhaps you are thinking of the error you get when a column is
ambiguously defined in a subquery and you then refer to it in the
outer query.