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

From: Jeff Papiez [MS] (jpapiezonline_at_microsoft.com)
Date: 02/16/04 

Date: Mon, 16 Feb 2004 12:42:06 -0800

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.
"Ed-it" <ed-it@euronet.nl> wrote in message
news:6d7b9e63.0402160949.142741af@posting.google.com...
> 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

Relevant Pages

  • Re: ADP Help
    ... And the stored procedure would be something like: ... > this, if all the SQL statements are on the SQL Server, how would the SQL ... I use that query as a base ... >>> query for almost all of my reports. ...
    (microsoft.public.access.adp.sqlserver)
  • Re: Promt user for criteria ?
    ... >faster that a passthrough query from my front end which is access. ... I have read reports claiming that Access will fetch a complete linked ... (to a SQL Server table or view) ... Hugo Kornelis, SQL Server MVP ...
    (microsoft.public.sqlserver.mseq)
  • Re: Reaching columns -in C#.net- in queries with multiple tables with the same column name
    ... SQL Server Integration Test Team ... > I see it also in the Query Analyzer. ... So my datagrids and reports and> sourcecode can't find the right column anymore, ...
    (microsoft.public.sqlserver.programming)
  • Re: SQL Server 7.0 8k rowsize limit
    ... reports to run the query. ... Crystal reports also comes back ... We were running it on SQL Server ... > have gotten error messages much earlier. ...
    (microsoft.public.sqlserver.programming)
  • Re: CONTAINS performance
    ... That said, and with the query plan, I can start to give you more ... relational join in the context of the free-text optimization, ... SQL Server tables. ...
    (microsoft.public.sqlserver.fulltext)