J. Doe J. Doe - 1 month ago 4
MySQL Question

mySQL inner join over multiple Tables

If i post this Lines of Code:

SELECT Cheese.Chesse_Name
FROM Customer
INNER JOIN Customer ON Customer.Customer_ID = Orders.Customer_ID
INNER JOIN Orders ON Orders.Orders_ID = OrderSandwich.Orders_ID
INNER JOIN OrderSandwich ON OrderSandwich.Sandwich_ID = Sandwich.Sandwich_ID
INNER JOIN Sandwich ON Sandwich.Cheese_ID = Cheese.Cheese_ID
WHERE Customer.CustomerID = 1;


All i get from the DB is an Error

Fehler

SQL-Befehl: Dokumentation

SELECT Cheese.Chesse_Name
FROM Customer
INNER JOIN Customer ON Customer.Customer_ID = Orders.Customer_ID
INNER JOIN Orders ON Orders.Orders_ID = OrderSandwich.Orders_ID
INNER JOIN OrderSandwich ON OrderSandwich.Sandwich_ID = Sandwich.Sandwich_ID
INNER JOIN Sandwich ON Sandwich.Cheese_ID = Cheese.Cheese_ID
WHERE Customer.CustomerID = 1
LIMIT 0, 25

MySQL meldet: Dokumentation
#1066 - Not unique table/alias: 'Customer'


I'm now trying this for several hours not to say the hole day.

Here is the DB Diagram:

enter image description here

I would be very greatful if somebody right away see the mistake i'm makeing

Sam Sam
Answer

You are trying to join Customer table with itself. Reorganize your JOIN queries like following.

SELECT Cheese.Cheese_Name
FROM Customer
INNER JOIN Orders ON Customer.Customer_ID = Orders.Customer_ID
INNER JOIN OrderSandwich ON Orders.Orders_ID = OrderSandwich.Orders_ID
INNER JOIN Sandwich ON OrderSandwich.Sandwich_ID = Sandwich.Sandwich_ID
INNER JOIN Cheese ON Sandwich.Cheese_ID = Cheese.Cheese_ID
WHERE Customer.Customer_ID = 1;

Remember, you are calling the JOIN statement, to join the mentioned table with the table above. For an example, if you call

Customer
INNER JOIN Orders ON Customer.Customer_ID = Orders.Customer_ID

You are joining the Customer Table with the Orders table. No need to mention Customer table twice.

Comments