CODEWITHSUNDEEP
mysqlphpmyadmininner-join
J. Doe
J. Doe

Reputation: 483

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

Upvotes: 0

Views: 1314

Answers (3)

ThisaruG
ThisaruG

Reputation: 3412

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.

Upvotes: 3

xQbert
xQbert

Reputation: 35343

Just seems to me you need to shift all the inner join tables up one and change the last to cheese. (removing the unneeded/desired 2nd join to customers)

SELECT Cheese.Chesse_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.CustomerID = 1;

Upvotes: 1

Mohammad Nurul Huda Rimon
Mohammad Nurul Huda Rimon

Reputation: 2525

Try this:

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

Upvotes: 1

Related Questions