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

From: Charles Hooper <hooperc2000_at_yahoo.com>
Date: 2 Mar 2007 11:16:18 -0800
Message-ID: <1172862978.008586.325080@n33g2000cwc.googlegroups.com>

On Mar 2, 11:07 am, mer..._at_gmx.net (Tobias Merler) wrote:
> 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

The results will look like this:
ID COL1 COL2 ID COL1 COL3 This would be similar to this:
SELECT

  A.ID,
  A.COL1,
  A.COL2,
  B.ID,
  B.COL1,
  B.COL3

FROM
  TABLEA A,
  TABLEB B
WHERE
  A.ID=B.ID;

The results of the above may not be too useful, so you might wat to change it as follows:
SELECT

  A.ID A_ID,
  A.COL1 A_COL1,
  A.COL2,
  B.ID,
  B.COL1 B_COL1,
  B.COL3

FROM
  TABLEA A,
  TABLEB B
WHERE
  A.ID=B.ID;

Charles Hooper
PC Support Specialist
K&M Machine-Fabricating, Inc. Received on Fri Mar 02 2007 - 13:16:18 CST