CODEWITHSUNDEEP
mysqlsqlleft-join
Bendihossan
Bendihossan

Reputation: 2497

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

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?

Upvotes: 5

Views: 23366

Answers (4)

Michael Berkowski
Michael Berkowski

Reputation: 270607

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 />

Upvotes: 7

Jasper De Bruijn
Jasper De Bruijn

Reputation: 1444

you are joining Stock on itself, which means the query doesn't know which Stock you are referencing. If this is what you want, make an alias for the joined table: JOIN Stock AS SuppliersStock

Upvotes: 1

El Barto
El Barto

Reputation: 929

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 />

Upvotes: 1

Ray
Ray

Reputation: 21905

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

Upvotes: 10

Related Questions