我厚吗？MySQL：LEFT JOIN 上的“非唯一表/别名”
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9668249/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Am I being thick? MySQL: "Not unique table/alias" on LEFT JOIN
提问by Bendihossan
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 StockIDas it's a common field name to Stockand SuppliersStock. Primary Key in Stock, Foreign Key in SuppliersStock
我有以下 SQL 语句返回“非唯一表/别名”。我相当肯定，我只是缺少明显的东西在这里，可能不是不够具体，当提到StockID，因为它是一个共同的字段名Stock和SuppliersStock。主键输入Stock,外键输入SuppliersStock
SELECT Stock.BuyingPrice, SuppliersStock.StockID, SuppliersStock.Quantity
FROM Stock
LEFT JOIN Stock on Stock.StockID = SuppliersStock.StockID
WHERE Stock.StockID = <some-integer />
The Stocktable has specific information about stock, Suppliershas info on suppliers and SuppliersStockhas information orders for new stock with foreign key references to Stockand Suppliers.
该Stock表包含有关库存的特定信息、Suppliers有关供应商的SuppliersStock信息以及带有外键引用Stock和 的新库存的信息订单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.
我想要做的是返回BuyingPrice(from Stock), StockID, Quantity(from SuppliersStock) 字段，以便我可以生成订购新库存的成本列表。
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?
免责声明：我知道，另一个关于 SQL 连接的问题。不要伤害我！我已经谷歌搜索，我已经搜索过，但我有点困惑，老实说，我试图寻找一个类似的问题，以了解更多关于我可以做些什么来解决这个问题的信息，但还是胜出。请帮忙？
回答by Ray
Your 'from' and 'left join' both refer to the the same table 'Stock'. Change one of them to refer to 'SupplierStock'
您的“from”和“left join”都指的是同一个表“Stock”。将其中之一更改为“SupplierStock”
回答by Michael Berkowski
Looks like you are skipping the alias to SuppliersStock, or it is a different table:
看起来您正在跳过别名SuppliersStock，或者它是一个不同的表：
/* 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 />
回答by El Barto
The problem is you're joining Stockwith Stockinstead of SuppliersStock, so when you say Stock.StockIDMySQL doesn't know which of the two are you referring to.
问题是你加入Stock了Stock而不是SuppliersStock，所以当你说Stock.StockIDMySQL 不知道你指的是这两者中的哪一个时。
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 />
回答by Jasper De Bruijn
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
您正在加入 Stock 本身，这意味着查询不知道您正在引用哪个 Stock。如果这是您想要的，请为连接表创建别名：JOIN Stock AS SuppliersStock
