CODEWITHSUNDEEP
sqljoininner-join
Geoffry
Geoffry

Reputation: 59

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.

Upvotes: 1

Views: 2267

Answers (3)

onedaywhen
onedaywhen

Reputation: 57023

One problem is that INNER JOIN was designed to generate duplicate columns in the result. This problem was solved in 1992 when the SQL standard introduced 'relational' join types that don't generate duplicate attributes:

SELECT OrderDetailID
  FROM OrderDetails 
       NATURAL JOIN Products
 WHERE SupplierID = 5;

Upvotes: 0

ScaisEdge
ScaisEdge

Reputation: 133360

You are using two time the same column so you must resove the amvbiguity eg: this way (using two distinct alias od1, od2)

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

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269693

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.

Upvotes: 1

Related Questions