CODEWITHSUNDEEP
sqlsql-servert-sql
ggwp
ggwp

Reputation: 9

Ambiguous column name in SQL Server

[]1

CREATE VIEW planView  
    SELECT 
        planID, planName, cost, quota, maxSpeed, shapedSpeed, typeID, typeName  
    FROM 
        Plans AS p 
    LEFT OUTER JOIN 
        accessType AS a ON p.typeID = a.typeID;  
GO

This keeps giving me error message, I tried to make two typeID equal to each other but still nothing

Upvotes: 0

Views: 11187

Answers (3)

Md. Suman Kabir
Md. Suman Kabir

Reputation: 5453

You can write like this :

CREATE VIEW planView  
    SELECT 
        planID, planName, cost, quota, maxSpeed, shapedSpeed, a.typeID, typeName  
    FROM 
        Plans AS p 
    LEFT OUTER JOIN 
        accessType AS a ON p.typeID = a.typeID;  
GO

Upvotes: 2

Sal-laS
Sal-laS

Reputation: 11639

You have a common column in your tables. It is ambiguous for SQL Server, because it does not know which table this column belongs to.

For instance if you have cost both in plan , and accessType table, then it gets confused about what it should return - plan.cost or accessType.cost ?

Solution:

Go to your select line

SELECT planID, planName, cost, quota, maxSpeed, shapedSpeed, typeID, typeName

and specify the name of table using . operator

I guess the problem comes from 

typeID

in the select because, you have used the Left outer join so, the values of the typeID in both tables are not the same always.

Upvotes: 3

Muhammad Hannan
Muhammad Hannan

Reputation: 2567

CREATE VIEW planView  
SELECT p.planID, p.planName, p.cost, p.quota, p.maxSpeed, p.shapedSpeed, a.typeID, a.typeName  
FROM Plans AS p LEFT OUTER JOIN accessType AS a  
ON p.typeID = a.typeID;  
GO

Your query should be like this.

Upvotes: -1

Related Questions