MySQL 复杂连接错误

Question

我在 SQL Fiddle 中编写了一些代码和所有表查询。我正在编写一个复杂的 JOIN 查询，并在 SQL Fiddle 上收到响应“Not unique table/alias: 'SalesInvoice'”

这是我的代码:

CREATE TABLE Customer
(CustomerID INT(255) NOT NULL AUTO_INCREMENT,
FirstName VARCHAR(255) NOT NULL,
LastName VARCHAR(255) NOT NULL,
StreetAddress VARCHAR(255) NOT NULL,
Apartment VARCHAR(255) NOT NULL,
City VARCHAR(255) NOT NULL,
State VARCHAR(2) NOT NULL,
ZipCode CHAR(9) NOT NULL,
HomePhone CHAR(10) NOT NULL,
MobilePhone CHAR(10) NOT NULL,
OtherPhone CHAR(10) NOT NULL,
PRIMARY KEY (CustomerID));
INSERT INTO Customer 
(FirstName, LastName, StreetAddress, Apartment, City, State,
ZipCode, HomePhone, MobilePhone, OtherPhone)
VALUES ('George', 'Engel', '190 Pine St', ' A 708 ', 'Polk', 
'WA', 98408, 4173231111, 1234567788, 5555551212);
SELECT CustomerID, (CONCAT (firstname, ' ', Lastname)) as FullName, StreetAddress, Apartment, City, State, ZipCode, Homephone, mobilephone, otherphone FROM customer;

CREATE TABLE Donut
(DonutID INT(255) NOT NULL AUTO_INCREMENT,
Name VARCHAR(255) NOT NULL,
Description VARCHAR(255) NOT NULL,
UnitPrice DECIMAL(3,2) NOT NULL,
PRIMARY KEY (DonutID));
INSERT INTO Donut (Name, Description, UnitPrice)
VALUES ('Plain', 'Plain Donut', '1.50'),
('Glazed', 'Glazed Donut', '1.75'),
('Cinnamon', 'Cinnamon Donut', '1.75'),
('Chocolate', 'Chocolate Donut', '1.75'),
('Sprinkle', 'Sprinkle Donut', '1.75'),
('Gluten-Free', 'Gluten-Free Donut', '2.00');
CREATE INDEX DonutName ON Donut (Name);

CREATE TABLE DonutOrder
(DonutOrderID INT (255) NOT NULL AUTO_INCREMENT,
DonutID INT(255) NOT NULL,
Quantity INT(255),
PRIMARY KEY (DonutOrderID, donutID),
INDEX Donut(donutID));
INSERT INTO DonutOrder (DonutID, Quantity)
VALUES ((SELECT DonutID FROM Donut WHERE DonutID = 1), 1),
   ((SELECT DonutID FROM Donut WHERE DonutID = 2), 5),
   ((SELECT DonutID FROM Donut WHERE DonutID = 3), 12),
   ((SELECT DonutID FROM Donut WHERE DonutID = 4), 3),
   ((SELECT DonutID FROM Donut WHERE DonutID = 5), 4),
   ((SELECT DonutID FROM Donut WHERE DonutID =6), 5);

CREATE TABLE SalesInvoice
(CustomerID INT(255) NOT NULL,
DonutOrderID INT(255) NOT NULL AUTO_INCREMENT,
Date date NOT NULL,
Spec_Hnd_Inst VARCHAR(255),
PRIMARY KEY (DonutOrderID),
INDEX Customer (CustomerID),
FOREIGN KEY (CustomerID) REFERENCES Customer(CustomerID),
INDEX DonutOrder (donutOrderID));
INSERT INTO SalesInvoice (CustomerID, DonutOrderID, Date,Spec_Hnd_Inst )   VALUES (1, 1, '20160319', 'Extra Sugar');

这是我的复杂联接查询

SELECT salesinvoice.donutorderid, 
       date, 
       spec_hnd_inst, 
       customer.customerid, 
       firstname, 
       lastname, 
       streetadress, 
       apartment, 
       city, 
       zipcode, 
       homephone, 
       mobilephone, 
       otherphone, 
       donutorder.donutorderid, 
       quantity, 
       donut.donutid, 
       NAME, 
       description, 
       unitprice, 
       customer.customerid, 
       firstname, 
       lastname, 
       streetadress, 
       apartment, 
       city, 
       zipcode, 
       homephone, 
       mobilephone, 
       otherphone 
FROM   customer 
       JOIN salesinvoice 
         ON customer.customerid = salesinvoice.customerid 
       JOIN salesinvoice 
         ON donutorder.donutorderid = salesinvoice.donutorderid 
       JOIN donut 
         ON donutorder.donutid = donut.donutid; 

任何指导都会有很大帮助。

提前致谢!

Answer 1

试试这个:

SELECT salesinvoice.donutorderid, 
       date, 
       spec_hnd_inst, 
       customer.customerid, 
       firstname, 
       lastname, 
       streetadress, 
       apartment, 
       city, 
       zipcode, 
       homephone, 
       mobilephone, 
       otherphone, 
       donutorder.donutorderid, 
       quantity, 
       donut.donutid, 
       NAME, 
       description, 
       unitprice, 
       customer.customerid, 
       firstname, 
       lastname, 
       streetadress, 
       apartment, 
       city, 
       zipcode, 
       homephone, 
       mobilephone, 
       otherphone 
FROM   customer 
       JOIN salesinvoice 
         ON customer.customerid = salesinvoice.customerid 
       JOIN donutorder
         ON donutorder.donutorderid = salesinvoice.donutorderid 
       JOIN donut 
         ON donutorder.donutid = donut.donutid;