B_Brown B_Brown - 1 month ago 9
SQL Question

Pesky ORA-00918 Error on WHERE function with IN function

New to SQL, first time out, and I need the community's guidance!.
I've been looking at this error for awhile now, as well documentation from W3Schools and the forum here...no dice! I'm creating a query to run on two different tables, COLUMNS have been selected/identified, same for TABLES, INNER JOIN setup between them, WHERE setup with an IN as well. The WHERE/IN function is where the column ambiguity error is getting in the way.

SELECT ORDERLINE.ORDER_ID, ORDERLINE.PRODUCT_ID,
PRODUCT.PRODUCT_ID, PRODUCT.PRODUCT_NAME, PRODUCT.PRODUCT_PRICE
FROM ORDERLINE, PRODUCT
INNER JOIN ORDERLINE ON ORDERLINE.PRODUCT_ID=PRODUCT.PRODUCT_ID
WHERE ORDERLINE.ORDER_ID IN ('1001');


Thoughts on where I went wrong here?

Answer

Your syntax is off a bit -- you are trying to join to the orderline table twice which is causing your ambiguity error. In general don't use commas in your from clause.

This should work using a single join:

SELECT OL.ORDER_ID, OL.PRODUCT_ID, P.PRODUCT_ID, P.PRODUCT_NAME, P.PRODUCT_PRICE 
FROM ORDERLINE OL
    JOIN PRODUCT P ON OL.PRODUCT_ID=P.PRODUCT_ID 
WHERE OL.ORDER_ID IN ('1001');