473,837 Members | 1,577 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Ambiguous Column Names in Multi-Table Join

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=E mployers.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,
"TableNameOrTab leAlias.ColumnN ame") 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

Jun 21 '06 #1
6 35582
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******@netsc ape.net> wrote in message
news:11******** **************@ m73g2000cwd.goo glegroups.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=E mployers.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,
"TableNameOrTab leAlias.ColumnN ame") 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

Jun 21 '06 #2

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

Jun 21 '06 #3
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=E mployers.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,
"TableNameOrTab leAlias.ColumnN ame") 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


Jun 21 '06 #4
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


Jun 21 '06 #5
Mike S (mg******@netsc ape.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****@sommarsk og.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
Jun 21 '06 #6
>> 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.

Jun 22 '06 #7

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
3874
by: b1cq7 | last post by:
I'm exporting MySQL data using a sql script containing:: SELECT * FROM (database name) INTO OUTFILE (filename) FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\r\n'; The file is created successfully but line 1 of the file does not contain the column names. Is there an option I need to enable in oder to produce this? Thanks for any assistance offered.
0
1134
by: mag31 | last post by:
I am creating a DataSet from an XmlDocument. The column names within the tables are not the same as the element names. This is causing me problems as I cannot then use the names for mapping data. An example would be an element: <SR_0020_ID>some text</SR_0020_ID> becomes column name: SR ID
0
1556
by: Sara | last post by:
We have a Cobol stored procedures on DB2 OS/390. We are able to access the stored procedures from a distributed platform Windows 2000/ Linux / Unix using DB2 Connect. But it does not return column names. It returns only column numbers. When we try to retrieve the metadata of resultset , it is returning NULL for column names or any other meta data from ResultSetMetaData Object. Can you please advise how to resolve this problem. Thanks...
1
6526
by: kk | last post by:
Hi, I am trying to set up scheduled export in DB2. DB2 seems to be missing the ability to export column names for CSV. Apparently 'method N' is only for ixf and wsf files. I have a C# and/or Python app I can modify but this seems like overkill for such a simple thing. Any suggestions would be appreciated! Keith
3
5514
by: Michael Gruner | last post by:
Hi, is there any idea about creating column names dynamically within a select statement and without stored procedures? We have some tables that have column names that consist of chars and numbers i.e.: Col0001, Col0002... Now I would like to have a function that returns the column name Col0001 if i call that function i.e. myfunc(1).
4
4273
by: Warren | last post by:
I am trying to capture some data from a MS Access DB which was created and populated by a VB program I did not write. I can grab all the data but I need to grab certain columns only, problem is the column names have spaces in them. IE Cabinet Height, Cabinet Depth, Cabinet Width. When I create the SQL statement during run time I tried the following from
2
2190
by: TanMan | last post by:
I have a large Access 2002 DB (mdb) with many linked tables pointing to a vendor database. The vendor has released a new version, and the column names in all the linked tables has changed. There are many queries, reports, forms, and modules in this mdb that refer to these linked tables. Is there an easy way to change the old column names to the new names? Something like a global find and replace? TIA
5
3004
by: Matt | last post by:
While i am looping the records how i do for each field name while(objRead.Read()) foreach( { match(x) Console.WriteLine(objRead.); }
0
1160
by: zacks | last post by:
I am trying to use the Microsoft Text Driver to access data in a text file via ODBC calls. Everything has worked fine so far. I am now trying to implement support for a file that has a special column name line as the first line. A monkey wrench thrown in is that one column must contain a mix of numbers and values that are an actual valid date. The main reason to use a schema.ini is when you do not have this column name line and you need to...
1
2249
by: bugs2bugs | last post by:
Hello, We're using SQL Server 2000 and Query Manager (not Visual Studio). When your query results are returned, the column names are displayed at the top, but this information is NOT available for cut/paste to Excel. One of the workarounds we thought of was to return the column names within the query itself, and thought there was perhaps an option or other feature of SQL Server that would allow us to put the column headings into Excel without...
0
10583
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
1
10642
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
10286
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
9420
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
1
7824
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
7013
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
5863
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
4481
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
4060
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.