Running into Error Code 1111 on MySQL how can I fix it?

Question

SELECT * FROM PRODUCT WHERE P_CODE = (SELECT P_CODE FROM LINE WHERE LINE_TOTAL > AVG(LINE_TOTAL));

Answer 1

You probably need this:

SELECT * 
FROM PRODUCT 
WHERE P_CODE in (SELECT P_CODE 
                 FROM LINE 
                 WHERE LINE_TOTAL > (select AVG(LINE_TOTAL) from LINE)
                );