Reaching columns -in C#.net- in queries with multiple tables with the same column name

  • Thread starter Thread starter Ed-it
  • Start date Start date
E

Ed-it

Hello,

I'm migrating my C#.net application from a MS Access-database to SQL
Server 2000. Now I found a problem.

In my code I use a lot (and I mean a lot) of queries with multiple
tables and some tables have columns with the same names ("id", "name"
etc). For example:

SELECT a.id, b.id FROM a INNER JOIN b ON a.id=b.id
or
SELECT a.id, b.id FROM a, b

In MS Access I can use the column names "a.id" and "b.id" (e.g. in
reports or datagrids). Now in SQL Server I see that there will be 2
columns named "id", there is no distinction, so no "a.id" and "b.id".
I see it also in the Query Analyzer. So my datagrids and reports and
sourcecode can't find the right column anymore, they are mapped to
"a.id" and "b.id". I found out that I can use aliasses (SELECT a.id as
'a.id', b.id as 'b.id' FROM...), but is there another way, so that I
don't have to change every query, and even worse, my datagrids and
reports?

Thanks in advance,
Ed
 
Ed-it said:
Hello,

I'm migrating my C#.net application from a MS Access-database to SQL
Server 2000. Now I found a problem.

In my code I use a lot (and I mean a lot) of queries with multiple
tables and some tables have columns with the same names ("id", "name"
etc). For example:

SELECT a.id, b.id FROM a INNER JOIN b ON a.id=b.id
Click to expand...

umm. arent a.id and b.id the same thing? Why select them twice?
 
You can give new names to your colums and reference them without a hitch..
for example, you could re-write your query like this:

SELECT a.id AS AId, b.id AS BId
FROM a
INNER JOIN b ON a.id = b.id

The "AS" is optional, though it helps me with readability.


--
Jeff Papiez, MCSD
SQL Server Integration Test Team
Microsoft Corp.

This posting is provided "AS IS" with no warranties, and confers no rights.
 
umm. arent a.id and b.id the same thing? Why select them twice?
Click to expand...
Sorry, bad example. This is maybe clearer:

SELECT a.name, b.name FROM a INNER JOIN b ON a.id=b.id

Ed
 
Jeff Papiez said:
You can give new names to your colums and reference them without a hitch..
for example, you could re-write your query like this:

SELECT a.id AS AId, b.id AS BId
FROM a
INNER JOIN b ON a.id = b.id

The "AS" is optional, though it helps me with readability.
Click to expand...
Yes, but the problem is, in my reports, datagrids and sourcecode I have
references to "a.id" and "b.id". And changing all the queries is one thing,
I don't want to change the datagrids, reports and sourcecode (and create new
bugs...). I was wondering if there is a way to migrate to SQL Server (or
MSDE) without changing all my queries. The problem now is that I get (in
this example) two identical columnnames (id) and if I use MS Access I get
"a.id" and "b.id".

And I realized I give a bit of a strange example, because a.id and b.id are
ofcourse the same. This is maybe better:
SELECT a.name, b.name FROM
FROM a
INNER JOIN b ON a.id = b.id

Ed
 
Back
Top