MySQL Complex Join Error

Question

I have written some code and all tables query in SQL Fiddle. I am writing a complex JOIN query and I get a response on SQL Fiddle "Not unique table/alias: 'SalesInvoice'"

Here is my Code:

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');

This is my Complex Join Query

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;

Any guidance would be a big help.

Thanks in advance!

Use table aliases for all the columns. Then you won't have this problem. — Gordon Linoff
– Gordon Linoff, Commented Mar 31, 2016 at 1:45

Ruslan · Accepted Answer · 2016-03-31 01:49:52Z

1

try this:

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;

answered Mar 31, 2016 at 1:49

Ruslan

10.2k15 gold badges59 silver badges92 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Gerrat Over a year ago

Looks better, but you should probably point out exactly what his issue was.

user3078797 Over a year ago

It works good, Customer name was redundant, I fixed that but now it works. I appreciate it!

Juan Carlos Oropeza · Accepted Answer · 2016-03-31 01:49:25Z

1

Probably you have these column repeat in multiple tables, just include the table name so MySQL know what table are you using.

   customer.city, 
   customer.zipcode, 
   customer.homephone, 
   customer.mobilephone, 
   customer.otherphone

answered Mar 31, 2016 at 1:49

Juan Carlos Oropeza

48.4k14 gold badges87 silver badges128 bronze badges

Collectives™ on Stack Overflow

MySQL Complex Join Error

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related