mysql - 不是唯一的表/别名 - SQL

Question

当我运行 join 语句时，出现错误“不是唯一表/别名‘Customer’”。我想显示我拥有的所有信息。我研究了 JOIN 语句，但找不到问题所在。

    SELECT Customer.CustomerID, Customer.FirstName, Customer.LastName,     Customer.StreetAddress,Customer.City,Customer.State,Customer.Zipcode, Customer.HomePhone,Customer.MobilePhone,Customer.OtherPhone, Pizza.PizzaID,
Pizza.PizzaName, Pizza.Description, Pizza.UnitPrice, OrderInformation.OrderID, OrderInformation.OrderDate, OrderItem.Quantity  


 FROM Customer

 JOIN OrderInformation ON OrderInformation.OrderID = OrderItem.OrderID
 JOIN Pizza ON Pizza.PizzaID = OrderItem.PizzaID
 JOIN Customer ON Customer.CustomerID = OrderInformation.CustomerID;

Answer 1

SELECT
...
FROM Customer
...
JOIN Customer

您选择FROM Customer，然后执行JOIN Customer，这意味着您现在有两个Customer 实例。当您引用诸如 Customer.CustomerID 之类的内容时，您的查询不知道您所引用的是 Customer 表的哪个迭代。

如果您确实需要对同一个表进行两次引用，则可以为其中一个引用指定一个别名。但是，由于您引用了 OrderItem 表，但从未 JOIN 或选择 FROM 它，我有预感其中一个 Customer 表应为 OrderItem。也许像这样...

SELECT ...

FROM OrderItem

JOIN OrderInformation ON OrderInformation.OrderID = OrderItem.OrderID
JOIN Pizza ON Pizza.PizzaID = OrderItem.PizzaID
JOIN Customer ON Customer.CustomerID = OrderInformation.CustomerID;