Am I being thick? MySQL: "Not unique table/alias" on LEFT JOIN

Question

I have the following SQL statement which returns "Not unique table/alias". I'm fairly sure I'm just missing something obvious here, possibly not being specific enough when referring to StockID as it's a common field name to Stock and SuppliersStock. Primary Key in Stock, Foreign Key in SuppliersStock

SELECT Stock.BuyingPrice, SuppliersStock.StockID, SuppliersStock.Quantity
FROM Stock
LEFT JOIN Stock on Stock.StockID = SuppliersStock.StockID 
WHERE Stock.StockID = <some-integer />

The Stock table has specific information about stock, Suppliers has info on suppliers and SuppliersStock has information orders for new stock with foreign key references to Stock and Suppliers.

What I want to do is return the BuyingPrice (from Stock), StockID, Quantity (from SuppliersStock) fields so I can produce a list of costs for ordering in new stock.

Disclaimer: I know, another question on SQL joins. Don't hurt me! I've Googled, I've searched but I'm a bit befuddled and I've honestly tried to look for a similar question to learn more about what I can do to solve this myself but come up trumps. Please help?

Answer 1

The problem is you're joining Stock with Stock instead of SuppliersStock, so when you say Stock.StockID MySQL doesn't know which of the two are you referring to.

I'm guessing you wanted to do this:

SELECT Stock.BuyingPrice, SuppliersStock.StockID, SuppliersStock.Quantity
FROM Stock
LEFT JOIN SuppliersStock on Stock.StockID = SuppliersStock.StockID 
WHERE Stock.StockID = <some-integer />

Answer 2

Your 'from' and 'left join' both refer to the the same table 'Stock'. Change one of them to refer to 'SupplierStock'

Answer 3

Looks like you are skipping the alias to SuppliersStock, or it is a different table:

/* If SuppliersStock is a different table */
SELECT Stock.BuyingPrice, SuppliersStock.StockID, SuppliersStock.Quantity
FROM Stock
LEFT JOIN SuppliersStock on Stock.StockID = SuppliersStock.StockID 
WHERE Stock.StockID = <some-integer />

/* If SuppliersStock is the same table, needing an alias */
SELECT Stock.BuyingPrice, SuppliersStock.StockID, SuppliersStock.Quantity
FROM Stock
LEFT JOIN Stock AS SuppliersStock on Stock.StockID = SuppliersStock.StockID 
WHERE Stock.StockID = <some-integer />