JOIN : ambiguous field names.

[LeBlatt]

I'm moving from access to SQL2000 using VB6 and ADODC.

With access, when joined tables had fields with identical names, the resulting column names were prefixed with the original table name. Now this doesnt happen with SQL2000. Its annoying to specify all fields in queries as I try to make generic queries that I can use on different tables.

So is there an option in SQL server to do that ?
thx alot

 

[cbirknh]

Try aliasing the table:

Select a.field1, a.field2, etc
From tblTableName a

This way you only need to change the field name and everything update.

 

[tlbroadbent]

You still need to alias column names if you select columns with the same name from each table. Writing "generic" queries may speed up development but it is poor practice if you want to have good query performance. Select only columns you require and alias those with duplicate names. Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/programmer.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.

 

[LeBlatt]

I figured a compromise : I started storing queries on the server, as views, so the VB code part stays generic. And I can select only useful fields in the views, and alias them.
Thx for the tips.

 

[tlbroadbent]

That is a great solution! Views provide a way to control access while giving users just what they need. Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/programmer.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.