What's wrong with this SQL query?

Favorite Answer

the error come from this line

select if(l.itemcode is not null, 1, 0)
from mas_newitem l

that produce result more than 1... that's answering why the error happen

Try the query 1 by 1 to see the error clearly

EDIT : your 2nd question

"I have two tables in a database; mas_newitem total rows are much lesser than the hq_itemtemp but the mas_newitem table should have at least column which contain the same data as the hq_itemtemp"

how do you compare for table that has less rows from your comparing table... you should have more table to compare with the less table... that's how you compare...

eg.

2 > 1 ... its normal but if 1 > 2 .. not normal...

so think about it and you should sort out which table to which table... i am pretty sure you will get the result soon, if not i cut my finger.. LOL...Show more

Investigate this subquery:

(select if(l.itemcode is not null, 1, 0)
from mas_newitem l)

It is likely returning more than one row.

EDIT:

More appropriately, you need to try to design that query such that it can ONLY return one row. If it's POSSIBLE for the query to return more than one row, the system might choke on it.

You may need to add more joins on the inside of those parentheses that I see are currently on the outside, in order to create that guarantee, when correlated with the rows returned by the enclosing query....Show more

Maybe

SELECT l.itemcode
FROM mas_newitem l
WHERE NOT EXISTS
(SELECT r.productcode FROM hq_itemtemp r WHERE
(select if(l.itemcode is not null, 1, 0)
from mas_newitem l) = 1
and r.productcode=l.itemcode and r.outletcode='005');...Show more