CODEWITHSUNDEEP
sqlms-accessms-access-2010
Jonathan
Jonathan

Reputation: 614

Error(Multiple Relations) While Coding SQL

I'm writing a sql query in Access 2010 and getting an error message saying that the field I am selecting is used in multiple relations so I need to select from just one table. I thought that I had the correct code for explicitly telling it which table to select from but I am still getting that error.

This is my code:

SELECT I.ingredientID, ingredientTypeCode, ingredientName, amount, unitCode
FROM Ingredient AS I
INNER JOIN BatchIngredient AS B ON I.ingredientID=B.ingredientID
ORDER BY ingredientID;

Shouldn't specifying I.ingredientID say that it will pull ingredientID from Ingredient and ignore BatchIngredient?

Upvotes: 1

Views: 36

Answers (1)

HansUp
HansUp

Reputation: 97131

If ingredientID is present in both tables, the db engine will find this ambiguous ...

ORDER BY ingredientID

I think you need ...

ORDER BY I.ingredientID

I would just go ahead and add the aliases in the SELECT clause, too. Replace each X with the appropriate alias.

SELECT I.ingredientID, X.ingredientTypeCode, X.ingredientName, X.amount, X.unitCode

Upvotes: 1

Related Questions