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?
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');