Ambiguous column name in self join

[bklurch]

Already typed my post once, having to do it again....

With the following code in MS SQL 2K:

SELECT Users.*,
Trainers.FirstName AS TFirstName,
Trainers.Surname AS TSurname,
Trainers.DisplayAs AS TDisplayAs,
Trainers.DIRealname AS TDIRealname,
Trainers.UserID AS TUserID,
Trainers.TrainerExpiry AS TTrainerExpiry
FROM Users
LEFT OUTER JOIN Users AS Trainers
ON Users.UserTrainerID = Trainers.UserID

I receive the following error:

Ambiguous column name 'Username'

(Username IS a field in the Users table for verification)

It works fine when I use it in enterprise manager for testing, but when called in an asp page, it returns the error.

It's doing my head in at the mo, so if anyone has any idea why it might be doing this I'll be greatly appreciative!

Thanks in advance

BKLurch
:?: :!: :?:

[hollystyles]

Hmm a tricky one, how are you testing it in Enterprise Manager? I have only ever used Query Analyser.

How are you connecting to the db from ASP ODBC, OLE DB or SQLSERVER?

Maybe trying a different provider might sort it.

[bklurch]

I'm using the 'SQL Server' driver and connect using:
Server.CreateObject("ADODB.Recordset")

Any help?

[bklurch]

Oh yes, the SQL statement is being pasted into the SQL editor in enterprise manager, SQL is verifying it is correct.

Previously, I had omitted the SELECT Users.* bit and just had SELECT * and that was understandably returning ambiguous columns via both methods.

[hollystyles]

Oh yes, the SQL statement is being pasted into the SQL editor in enterprise manager, SQL is verifying it is correct

Right so it's parsing the statement, but that's not the same as using it.
Try pasting and running it in Query Analyser.

I would replace the Users.* with a full list of columns you require, although I can't see why that should make a difference, but try it anyway.

[hollystyles]

Are you referencing the recordset like this:

rs.fields("Username")

Possibly Username is in the select list twice?

(my ASP is rusty as I'm all .NET now)

[bklurch]

All sorted, twas to do with selecting fields.

Thanking you muchly.