CODEWITHSUNDEEP
mysqlsqlmysql-workbenchmysql-error-1054
Raja
Raja

Reputation: 21

Error Code: 1054 : Unknown column 'Orders.Customer_ID' in 'on clause'

I am constantly getting this Error Even there is not any Syntactical Error shown.

I made two table namely 'Customers' and 'Orders' and when i am trying to use FULL JOIN as given below in mySQL Workbench.

SELECT DATE,NAME,Order_Price,Delivery_Address
FROM Orders
FULL JOIN Customers
ON Orders.Customer_ID = Customers.Customer_ID;

Please see the Linked screenshot of my both Table:-

Customer Table

enter image description here

Orders Table

enter image description here

It shows the error 1054.

Upvotes: 0

Views: 4230

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269693

As Strawberry points out, full join is not supports in MySQL. However, it is probably not at all necessary for your query. It is hard to imagine orders where the Customer_Id is not valid. You might have customers with no orders, but this should do wha tyo uwant:

SELECT DATE, NAME, Order_Price, Delivery_Address  -- you should qualify all column references
FROM Customers c LEFT JOIN
     Orders o
     ON o.Customer_ID = c.Customer_ID;

If you were in the habit of always using table aliases, then you would find that this query:

SELECT DATE, NAME, Order_Price, Delivery_Address  -- you should qualify all column references
FROM Customers c FULL JOIN
     Orders o
     ON o.Customer_ID = c.Customer_ID;

returns an error.

Upvotes: 0

Strawberry
Strawberry

Reputation: 33935

FULL [OUTER] JOIN isn't a thing in MySQL, so your query is instead misinterpreted as follows:

SELECT date
     , name
     , order_price
     , delivery_address
  FROM orders full
  JOIN customers 
   ON orders.Customer_ID = Customers.Customer_ID;

...where full is an alias for orders. If you change the last line to...

ON full.Customer_ID = Customers.Customer_ID;

... it will work; it just won't return the result you desire. But alternatives for FULL OUTER JOIN in MySQL are widely discussed elsewhere, so I'll leave it at that.

Upvotes: 2

Related Questions