(SQL) I have to use the IN operator to get information from 2 different tables-CodePudding

this is my first year learning SQL so bear with me :)

A question in an assignment I was given is asking me this: "Use the IN operator to find the number and name of each customer that placed an order on October 12, 2015." So, here's what I tried using:

SELECT CUSTOMER_NUM, CUSTOMER_NAME
FROM CUSTOMER
WHERE ORDER_DATE IN
(SELECT ORDER_DATE
FROM ORDERS
WHERE ORDER_DATE = '12-OCT-15');

When I ran this code, I got an error:

ORA-00904: "ORDER_DATE": invalid identifier

00000 - "%s: invalid identifier

I've looked at the examples my professor provided in our notes and I've tried changing things around (that just gave an error about Literals). I need data from two different tables as CUSTOMER_NUM and CUSTOMER_NAME are in the Customer table and ORDER_DATE is in the Orders table (CUSTOMER_NUM is also in the Orders table)

Thanks for any help. Sorry if this is a dumb question, I just don't know what to do from here :)

Let me know if I need to add more information from the data I'm using so you can help me.

CodePudding user response：

SELECT CUSTOMER_NUM, CUSTOMER_NAME FROM CUSTOMER WHERE ORDER_DATE IN ('12-OCT-15'); Try this

CodePudding user response：

SELECT DISTINCT CUSTOMER.CUSTOMER_NUM, 
       CUSTOMER.CUSTOMER_NAME
FROM CUSTOMER
INNER JOIN ORDERS ON CUSTOMER.CUSTOMER_NUM = ORDERS.CUSTOMER_NUM
WHERE ORDER.ORDER_DATE IN ('2015-10-12');

I'm used to POSTGRESQL so i'm not sure if this syntax will work with oracle but you get the point.