SQL refusing to do a join even when every identifier is valid? (ORA-00904)-CodePudding

Made this account just to ask about this question after being unable to find/expending the local resources I have, so I come to you all.

I'm trying to join two tables - ORDERS and CUSTOMER - as per a question on my assignment

For every order, list the order number and order date along with the customer number, last name, and first name of the customer who placed the order.

So I'm looking for the order number, date, customer number, and the full name of customers.

The code goes as such

SELECT ORDERS.ORDR_ORDER_NUMBER, ORDERS.ORDR_ORDER_DATE, ORDERS.ORDR_CUSTOMER_NUMBER, CUSTOMER.CUST_LAST, CUSTOMER.CUST_FIRST
FROM ORDERS, CUSTOMER
WHERE ORDERS.ORDR_CUSTOMER_NUMBER = CUSTOMER.CUST_CUSTOMER_NUMBER;

I've done this code without the table identifiers, putting quotation marks around ORDERS.ORDR_CUSTOMER_NUMBER, aliases for the two tables, and even putting a space after ORDR_ in both SELECT & WHERE for laughs and nothing's working. All of them keep coming up with the error in the title (ORA-00904), saying [ORDERS.]ORDR_CUSTOMER_NUMBER is the invalid identifier even though it shouldn't be.

Here also are the tables I'm working with, in case that context is needed for help.

CodePudding user response：

You have to add JOIN or INNER JOIN to your query. Because the data comes from two different tables the WHERE clause will not select both.

FROM Orders INNER JOIN Customers ON Orders.order_Customer_Number = Customer.Cust_Customer_Number

CodePudding user response：

Anyway, the query that produces the result you want should take the form:

select
  o.ordr_order_number,
  o.ordr_order_date,
  c.cust_customer_number,
  c.cust_last,
  c.cust_first
from orders o
join customer c on c.cust_customer_number = o.ordr_customer_number

As you see the query becomes a lot easier to read and write if you use modern join syntax, and if you use table aliases (o and c).