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

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
 
R

Rick Mogstad

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

umm. arent a.id and b.id the same thing? Why select them twice?
 
J

Jeff Papiez [MS]

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.
 
E

Eddie Baby

umm. arent a.id and b.id the same thing? Why select them twice?
Sorry, bad example. This is maybe clearer:

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

Ed
 
E

Eddie Baby

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.
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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top