Ambiguous column name 'ProductNumber'

ambiguous column name inner join
ambiguous column name sqlite
ambiguous column name left join
ambiguous column name ssis
ambiguous column name update sql
ambiguous column name businessentityid
sqlalchemy ambiguous column name
sqlexception ambiguous column name

Can't figure out why my question 3 is getting the error mentioned above.

Below is my code

/* Question 1 */
CREATE TABLE CUSTOMERS 
( 
    CustomerID INT PRIMARY KEY, 
    CustFirstName VARCHAR(50) NOT NULL, 
    CustLastName VARCHAR(50) NOT NULL, 
    CustStreetAddress VARCHAR(50) NOT NULL, 
    CustCity VARCHAR(50) NOT NULL, 
    CustState VARCHAR(26) NOT NULL, 
    CustZipCode INT NOT NULL, 
    CustAreaCode INT NOT NULL, 
    CustPhoneNumber VARCHAR (26) NOT NULL
);  

CREATE TABLE EMPLOYEES 
( 
    EmployeeID INT PRIMARY KEY, 
    EmpFirstName VARCHAR(50) NOT NULL, 
    EmpLastName VARCHAR(50) NOT NULL, 
    EmpCity VARCHAR (50) NOT NULL, 
    EmpState VARCHAR(26) NOT NULL, 
    EmpZipCode INT NOT NULL, 
    EmpAreaCode INT NOT NULL, 
    EmpPhoneNumber VARCHAR(26) NOT NULL, 
    EmpBirthDate DATE NOT NULL
); 

CREATE TABLE ORDERS
( 
    OrderNumber INT PRIMARY KEY, 
    OrderDate DATE NOT NULL, 
    ShipDate DATE NOT NULL, 
    CustomerID INT NOT NULL, 
    EmployeeID INT NOT NULL, 

    FOREIGN KEY(EmployeeID) REFERENCES EMPLOYEES(EmployeeID), 
    FOREIGN KEY(CustomerID) REFERENCES CUSTOMERS(CustomerID)
); 

CREATE TABLE CATEGORIES
( 
    CategoryID INT PRIMARY KEY, 
    CategoryDescription VARCHAR(255) NOT NULL
); 

CREATE TABLE PRODUCTS
(
    ProductNumber INT PRIMARY KEY, 
    ProductName VARCHAR(50) NOT NULL, 
    ProductDescription VARCHAR(255) NOT NULL,
    RetailPrice INT NOT NULL, 
    QuantityOnHand INT NOT NULL, 
    CategoryID INT NOT NULL, 

    FOREIGN KEY(CategoryID) REFERENCES CATEGORIES (CategoryID)
);    

CREATE TABLE ORDER_DETAILS
( 
    OrderNumber INT NOT NULL, 
    ProductNumber INT NOT NULL, 
    QuotedPrice INT NOT NULL, 
    QuantityOrdered INT NOT NULL, 

    PRIMARY KEY (OrderNumber, ProductNumber), 
    FOREIGN KEY (OrderNumber) REFERENCES ORDERS(OrderNumber), 
    FOREIGN KEY(ProductNumber) REFERENCES PRODUCTS(ProductNumber)
);   

CREATE TABLE VENDORS
(
    VendorID INT PRIMARY KEY, 
    VendName VARCHAR(100) NOT NULL, 
    VendStreetAddress VARCHAR(50) NOT NULL, 
    VendCity VARCHAR(50) NOT NULL, 
    VendState VARCHAR(26) NOT NULL, 
    VendFaxNumber VARCHAR(50) NOT NULL, 
    VendWebPage VARCHAR(100) NOT NULL, 
    VendEmailAddress VARCHAR(100) NOT NULL
); 

CREATE TABLE PRODUCT_VENDORS
(
    ProductNumber INT NOT NULL, 
    VendorID INT NOT NULL, 
    WholeSalePrice INT NOT NULL,
    DaysToDeliver INT NOT NULL, 

    PRIMARY KEY(ProductNumber, VendorID), 
    FOREIGN KEY(ProductNumber) REFERENCES PRODUCTS(ProductNumber), 
    FOREIGN KEY(VendorID) REFERENCES Vendors(VendorID)
); 

/* QUESTION 2 */

SELECT 
    OrderDate, CustFirstName, CustLastName
FROM 
    CUSTOMERS C, ORDERS O
WHERE 
    C.CustomerID = O.OrderNumber; 

/* QUESTION 3 */

SELECT 
    ProductNumber, WholeSalePrice, VendName 
FROM 
    PRODUCTS P, PRODUCT_VENDORS PV, VENDORS V 
WHERE 
    P.PRODUCTNUMBER = PV.ProductNumber AND PV.VendorID = V.VendorID; 

I got the error

Ambiguous column name 'ProductNumber'

Because there are two table have column name of ProductNumber in your query you need to tell DB engine which column you want to get.

Also, use JOIN instead of , comma CROSS JOIN, because Join is more clear than a comma on the relationship between the two tables

SELECT PV.ProductNumber, WholeSalePrice, VendName 
FROM PRODUCTS P 
JOIN PRODUCT_VENDORS PV ON P.PRODUCTNUMBER = PV.ProductNumber
JOIN VENDORS V ON PV.VendorID = V.VendorID; 

Query error with ambiguous column name in SQL, I think you have ambiguity only in InvoiceID. Other fields seem distinct. So try This​. I just replace InvoiceID with Invoices.InvoiceID. SELECT  Ambiguous column names When joining tables, we use aliases in the SELECT statement to indicate the source tables of the selected columns, with each column name prefixed with the table name alias. The following query joins the Players and PlayerStats tables to return total points by PlayerName and Country for all players from Australia.

Your query stipulates SELECT ProductNumber From two tables that both have ProductNumber. In the SELECT columns list, prefix ProductNumber with the table name from which you want to receive the data.

also, Have a look at your question 2. I don't think you really want to join customer id to order number, and you should use JOIN syntax intead of joining in the WHERE clause

What does the error "Ambiguous column name 'id'" mean , The error is referring to "id" in the first line of your query. SQL Server doesn't know whether you are referring to tbldata.id or tblimg.id. It is good  ORA-00918 column ambiguously defined. Cause: A column name used in a join exists in more than one table and is thus referenced ambiguously. In a join, any column name that occurs in more than one of the tables must be prefixed by its table name when referenced. The column should be referenced as TABLE.COLUMN or TABLE_ALIAS.COLUMN.

Ambiguous error means that you are calling a certain field in which exist in both Table and the SQL has no idea where to get it. See, with your query, you're just literally calling the ProductNumber field in which the SQL has no idea where to get it since there are ProductNumber fields on both Tables, and you didn't specify any table.

so it is better when you have same column in multiple tables use table preface 1st then column like table1.col1,table2.col2 so in your case

 SELECT p.ProductNumber, WholeSalePrice, v.VendName     
   FROM PRODUCTS P join 
  PRODUCT_VENDORS PV  on P.PRODUCTNUMBER = PV.ProductNumber //
  join   VENDORS V on PV.VendorID = V.VendorID //use join instead your's

Ambiguous column name '%.*ls'., Server: Msg 209, Level 16, State 1, Line 1 Ambiguous column name '<Column Name>'. Causes: This error happens when you are joining 2 or more tables in a  My goal is to find all vacation balance over 200 and all active userID but I keep getting an ambiguous column name for userID. My query does work if i try to pull anything else, but I want to pull UserID instead. Any help on my query will be appreciated. Here is my table and its columns: Table USER. ID (PK) UserID Firstname Lastname Email Active (Bool)

Why this error? SQL Error: ambiguous column name: ModelName , Why this error? SQL Error: ambiguous column name: ModelName. SELECT MakeName, ModelName, VIN, StickerPrice FROM Make INNER  You are using two tables, a and b, both of which have column id. – hamstergene Mar 25 '14 at 11:34 In your query alias name a and b both for same table 'import', may this cause problem – Jaihind Mar 25 '14 at 13:40

Why am I seeing an "ambiguous column name" error when I'm trying , I am seeing an "ambiguous column name" error when I'm trying to join tables if the column name is spelled correctly? 2 Answers  Prefix the ID column in the where clause with the table name. It is confused because ID is in both tables and it doesn't know which one you mean. WHERE student_info.id=1 or. WHERE student_class.id=1 depending on which one you meant when you wrote the query.

Ambiguous column name - SQL, I keep getting an Ambiguous Column Name error, and putting "Students." in front of StudentID in the WHERE clause just causes another set of errors. Here's my  Clearly, from the code youve supplied we can easily tell there is no problem. You need to provide more code and possibly all combinations ie. module names, sub names, function names etc. Scan through your code with CTRL + F and search for SixTables – user2140173 Nov 28 '13 at 8:34

Comments
  • Bad habits to kick : using old-style JOINs - that old-style comma-separated list of tables style was replaced with the proper ANSI JOIN syntax in the ANSI-92 SQL Standard (more than 25 years ago) and its use is discouraged
  • Also, it's good practice to preface all your columns with the table name or table prefix when joining multiple columns.