Posted by mail on 07/30/06 17:31

Dan Guzman wrote:
> If you have duplicate column names in an ADO recordset, you can use ordinal
> position so that the reference is unambiguous:
>
> RECORDSET(0)
> RECORDSET(1)
>
> However, it's better to specify a column alias so that all column names in
> the result are unique:
>
> SELECT
> t1.MyColumn AS Table1_MyColumn,
> t2.MyColumn AS Table2_MyColumn
> FROM dbo.MyTable1 AS t1
> JOIN dbo.MyTable2 AS t2 ON
> t2.MyColumn = t1.MyColumn
>
> RECORDSET("Table1_MyColumn")
> RECORDSET("Table2_MyColumn")
>
> --
> Hope this helps.
>
> Dan Guzman
> SQL Server MVP
>
> <mail@jazzis.com> wrote in message
> news:1154265500.655946.116740@p79g2000cwp.googlegroups.com...
> > Urgent help needed!
> >
> > I moved an application from ASP+ACCESS to ASP+MS SQLSERVER and I have
> > the following problem:
> >
> > If the join on two tables results on duplicate colum names (which
> > appear in both tables) I could reference them by using:
> >
> > RECORDSET("TABLENAME.COLUMNAME")
> >
> > However with SQLServer if I try this kind of reference I get an error
> > message.
> >
> > How can between two colums with the same name from two differen tables?
> >
> > Thanks in advance!
> >
> > Adam
> >

Thanks Dan for your help.

I do know all this of course, I was just surprised by the difference in
behavior between MS Sqlserver and other database, which return the
table name as part of the name.

I have read quite a lot on the subject and some people claim that a
colum name should be UNIQUE in a database, i.e. rather than having

table1.column
table2.column

the design should be

table1.colum1
table2.colum2

This would solve ANY ambiguity as to colum names especially in joins.

What is your opinion on that?

P.S. Classis DB / SQL literature does not mention this as a
prerequisite for proper DB design.

Adam

[Back to original message]