Why is this SQL Query causing an “Ambiguous column name” error?
I have a SQL query which fails to execute:
select p.PersonID, CreatedDate, * from dbo.Person p join dbo.PotentiallyFraudulentPeople pfp on p.PersonID= pfp.PersonID order by CreatedDate
The Person table has a PK of PersonID (int). The PotentiallyFraudulentPeople view is a query of the Person table joined with some other tables to decide if we trust a person or not. The PotentiallyFraudulentPeople view only has one column: PersonID.
When I try to execute this query, I get this error:
Msg 209, Level 16, State 1, Line 3 Ambiguous column name 'CreatedDate'.
I understand that this error is telling me that the CreatedDate column name is ambiguous and I need to preface it with my table's alias, 'p'.
This query works:
select p.PersonID, CreatedDate, * from dbo.Person p join dbo.PotentiallyFraudulentPeople pfp on p.PersonID= pfp.PersonID order by p.CreatedDate
What I don't understand is why I need to use the 'p' alias in the ORDER BY statement and not in the SELECT column list. Also, I don't understand why I need to use the table alias at all since the PotentiallyFraudulentPeople view doesn't even have a CreatedDate column.
Can anyone explain this odd behavior?
I am using SQL Server 2008 and SSMS to execute the query.
UPDATE Also, I tried removing the CreatedDate column from my SELECT column list and then the query no longer requires the 'p' alias in the ORDER BY. So this query works as well:
select p.PersonID, * from dbo.Person p join dbo.PotentiallyFraudulentPeople pfp on p.PersonID= pfp.PersonID order by CreatedDate
You are selecting the CreatedDate column twice.
- Explicitly via CreatedDate.
- Implicitly via *.
It doesn't know which occurence you want to sort on - and it obviously doesn't realize that both occurences refer to the same column.