Ambiguous Column Names in Multi-Table Join

[Mike S]

Hi all,

A (possibly dumb) question, but I've had no luck finding a definitive
answer to it. Suppose I have two tables, Employees and Employers, which
both have a column named "Id":

Employees
-Id
-FirstName
-LastName
-SSN
etc.

Employers
-Id
-Name
-Address
etc.

and now I perform the following join:

SELECT Employees.*, Employers.*
FROM Employees LEFT JOIN Employers ON (Employees.Id=Employers.Id)

The result-set will contain two "Id" columns, so SQL Server will
disambiguate them; one column will still be called "Id", while the
other will be called "Id1." My question is, how are you supposed to
know which "Id" column belongs to which table? My intuition tells me,
and limited testing seems to indicate, that it depends on the order in
which the table names show up in the query, so that in the above
example, "Id" would refer to Employees.Id, while "Id1" would refer to
Employers.Id. Is this order guaranteed?

Also, why does SQL Server use such a IMO brain-damaged technique to
handle column name conflicts? In MS Access, it's much more
straightforward; after executing the above query, you can use
"Employees.Id" and "Employers.Id" (and more generally,
"TableNameOrTableAlias.ColumnName") to refer to the specific "Id"
column you want, instead of "Id" and "Id1" -- the
"just-tack-on-a-number" strategy is slightly annoying when dealing with
complex queries.

--
Mike S

[Jack Vamvas]

You could :
SELECT E1.id as "Employees_ID", E2.id as "Employers_ID"
FROM Employees as E1 LEFT JOIN Employers AS E2 ON (E1.Id=E2.Id)

--
----
Jack Vamvas
___________________________________
Receive free SQL tips - www.ciquery.com/sqlserver.htm
___________________________________
"Mike S" <mg******@netscape.net> wrote in message
news:11**********************@m73g2000cwd.googlegr oups.com...

Hi all,

A (possibly dumb) question, but I've had no luck finding a definitive
answer to it. Suppose I have two tables, Employees and Employers, which
both have a column named "Id":

Employees
-Id
-FirstName
-LastName
-SSN
etc.

Employers
-Id
-Name
-Address
etc.

and now I perform the following join:

SELECT Employees.*, Employers.*
FROM Employees LEFT JOIN Employers ON (Employees.Id=Employers.Id)

The result-set will contain two "Id" columns, so SQL Server will
disambiguate them; one column will still be called "Id", while the
other will be called "Id1." My question is, how are you supposed to
know which "Id" column belongs to which table? My intuition tells me,
and limited testing seems to indicate, that it depends on the order in
which the table names show up in the query, so that in the above
example, "Id" would refer to Employees.Id, while "Id1" would refer to
Employers.Id. Is this order guaranteed?

Also, why does SQL Server use such a IMO brain-damaged technique to
handle column name conflicts? In MS Access, it's much more
straightforward; after executing the above query, you can use
"Employees.Id" and "Employers.Id" (and more generally,
"TableNameOrTableAlias.ColumnName") to refer to the specific "Id"
column you want, instead of "Id" and "Id1" -- the
"just-tack-on-a-number" strategy is slightly annoying when dealing with
complex queries.

--
Mike S

[Mike S]

Jack Vamvas wrote:

You could :
SELECT E1.id as "Employees_ID", E2.id as "Employers_ID"
FROM Employees as E1 LEFT JOIN Employers AS E2 ON (E1.Id=E2.Id)

I was actually thinking about doing it that way, just aliasing all the
columns. I was hoping to avoid that because it would involve changing a
number of existing queries/program code - plus most of the queries are
'SELECT * FROM Table" type queries, so to produce the same results, I'd
have to alias every single column in each table. For this particular
project, I think it might be easier to deal with names like Id, Id1,
Id2, etc., even though it's not very readable...oh well, just a matter
of adding extra comments to the source code ;-)

--
Mike S

[SQL]

Run this in query analyzer

select * from
(select 1 as id)a
cross join (select 2 as id) b

as you can see the result set is this
id id
----------- -----------
1 2
id is displayed twice, where do you get id1 is it client site?
I ran the same query in enterprise manager and I see id twice

Denis the SQL Menace
http://sqlservercode.blogspot.com/
Mike S wrote:

Hi all,

A (possibly dumb) question, but I've had no luck finding a definitive
answer to it. Suppose I have two tables, Employees and Employers, which
both have a column named "Id":

Employees
-Id
-FirstName
-LastName
-SSN
etc.

Employers
-Id
-Name
-Address
etc.

and now I perform the following join:

SELECT Employees.*, Employers.*
FROM Employees LEFT JOIN Employers ON (Employees.Id=Employers.Id)

The result-set will contain two "Id" columns, so SQL Server will
disambiguate them; one column will still be called "Id", while the
other will be called "Id1." My question is, how are you supposed to
know which "Id" column belongs to which table? My intuition tells me,
and limited testing seems to indicate, that it depends on the order in
which the table names show up in the query, so that in the above
example, "Id" would refer to Employees.Id, while "Id1" would refer to
Employers.Id. Is this order guaranteed?

Also, why does SQL Server use such a IMO brain-damaged technique to
handle column name conflicts? In MS Access, it's much more
straightforward; after executing the above query, you can use
"Employees.Id" and "Employers.Id" (and more generally,
"TableNameOrTableAlias.ColumnName") to refer to the specific "Id"
column you want, instead of "Id" and "Id1" -- the
"just-tack-on-a-number" strategy is slightly annoying when dealing with
complex queries.

--
Mike S

[Stu]

No trying to rub salt in your wounds, but this wouldn't be an issue if
you followed a couple of standard programming practices:

1. Avoid SELECT * in production code. Always specify column names
(and aliases if you'd like); it'll make maintenance much easier, and
keep you from having to recompile dependent views if you add or remove
a column at a later date.

2. Use stored procedures as a data access method rather than SQL in
the application; much easier to adjust a stored procedure in one place
rather than several SQL statements throughout your application (not to
mention the security benefits).

There are exceptions to every rule, of course, and I'm not in your
shoes, but it sounds like you need to tighten up your code a bit.

Stu

Mike S wrote:

Jack Vamvas wrote:

You could :
SELECT E1.id as "Employees_ID", E2.id as "Employers_ID"
FROM Employees as E1 LEFT JOIN Employers AS E2 ON (E1.Id=E2.Id)

I was actually thinking about doing it that way, just aliasing all the
columns. I was hoping to avoid that because it would involve changing a
number of existing queries/program code - plus most of the queries are
'SELECT * FROM Table" type queries, so to produce the same results, I'd
have to alias every single column in each table. For this particular
project, I think it might be easier to deal with names like Id, Id1,
Id2, etc., even though it's not very readable...oh well, just a matter
of adding extra comments to the source code ;-)

--
Mike S

[Erland Sommarskog]

Mike S (mg******@netscape.net) writes:

I was actually thinking about doing it that way, just aliasing all the
columns. I was hoping to avoid that because it would involve changing a
number of existing queries/program code - plus most of the queries are
'SELECT * FROM Table" type queries, so to produce the same results, I'd
have to alias every single column in each table. For this particular
project, I think it might be easier to deal with names like Id, Id1,
Id2, etc., even though it's not very readable...oh well, just a matter
of adding extra comments to the source code ;-)

If you have a lot of SELECT * then you have a lot of code to modify.
SELECT * does not belong in production code.

I don't know where you got the idea of Id1 from; SQL Server returns a
result set with two columns that have the same name.

Besides, if the id is a join column, there is little reason to return
it twice...
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[--CELKO--]

>> A (possibly dumb) question, but I've had no luck finding a definitive answer to it. Suppose I have two tables, Employees and Employers, which both have a column named "Id" <<

Well, first of all, kill the stupid bastard that used "id" as a column
name, since he never read ISO-11179 or any book on BASIC data modeling.
This is not a data element name; it is too vague (identifier of
what??) and it appears EVERYWHERE, so it is a meaningless exposed
physical locator.

Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are. Sample data is also a good idea, along with clear
specifications. It is very hard to debug code when you do not let us
see it. If you were polite and had a valid schema, would it look like
this?

CREATE TABLE Personnel -- note the use of a collective name for a set
(ssn CHAR(9) NOT NULL PRIMARY KEY, -- legal requirement!
last_name VARCHAR(20) NOT NULL,
first_name VARCHAR(20) NOT NULL,
.. );

CREATE TABLE Employers
(duns_nbr CHAR(9) NOT NULL PRIMARY KEY, -- industry standards!!
employer_name VARCHAR(20) NOT NULL,
..);

and now I perform the following join: <<
I hope not! it makes no sense. What is the relationship between two
values in totally different domains?? Gee, we need a table for that
...
The result-set will contain two "Id" columns, so SQL Server will disambiguate them; one column will still be called "Id", while the other will be called "Id1." My question is, how are you supposed to know which "Id" column belongs to which table? <<
By having a proper data model in which different data elements have
different names. What you have here is a "Vague, Magical, Universal
one-size-fits-all Kabalah Number" on tables, when you need a third
table called "Employment" with the employees and employers identifiers
in its columns. Basically the engine is tryitn to do the best it can
with your crappy design.
My intuition tells me, and limited testing seems to indicate, that it depends on the order in which the table names show up in the query, so that in the above example, "Id" would refer to Employees.Id, while "Id1" would refer to Employers.Id. Is this order guaranteed? <<

This is one of MANY reasons good programmers do not do this kind of
crappy design. The vendor is free to do anything they wish with the
display of such data. Nobody agrees. Nobody does it the same in
different releases. The best you can do is alias one of the columns.