|
|
Posted by Jack Vamvas on 06/21/06 16:35
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" <mgspross@netscape.net> wrote in message
news:1150907102.154838.278240@m73g2000cwd.googlegroups.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
>
Navigation:
[Reply to this message]
|