Geoffry Geoffry - 7 days ago 6
SQL Question

Ambiguous Column Name Even Though Column Name and Table are Declared?

I am trying to create a query based on trying to find a value in one column of a table based on the values of another column in another table. This is the code I have written so far,

SELECT OrderDetails.OrderDetailID FROM OrderDetails
INNER JOIN OrderDetails
ON Products.ProductID = OrderDetails.ProductID
WHERE Products.SupplierID="5";


By executing the code, I want to find the OrderDetailID of the row in the OrderID Table where the SupplierID in the Products table is 5. For example,

Highlighted Products Table

The SupplierID of "Queso Cabrales" is 5 and its ProductID is 11. This corresponds to the foreign key in the table OrderDetail of 11 and therefore I want the primary key of that row to be returned. In this case 1.

Highlighted OrderDetails Table

At the moment I get an "ambiguous column name" error in the first line even though I have declared both the table and the column name. Also, how am I meant to ask SQL to fetch the data regarding other tables. I know I am mean't to use "INNER JOIN" but how do I execute so the WHERE command can be used.

Answer

You have OrderDetails twice in the FROM clause but no Products. I think you mean:

SELECT od.OrderDetailID
FROM OrderDetails od INNER JOIN
     Products p
     ON p.ProductID = od.ProductID
WHERE p.SupplierID = 5;

Notes:

  • Table aliases make the query easier to write and read (the od and p).
  • Don't use delimiters around numeric constants. I assume that SupplierId is a number, so I removed the double quotes.