What's wrong with this SQL query?
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)
and r.productcode=l.itemcode and r.outletcode='005');
Error Code : 1242
Subquery returns more than 1 row
(16 ms taken)
MySQL query using SQLyog
Additional Details
I did something like this before
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 limit 1)
and r.productcode=l.itemcode and r.outletcode='005')
limit 0, 10;
I does produce results but I'm sure that it's not what I want.
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. The total column are not the same either. So far I think I can produce a result which shows how many rows in hq_itemtemp that doesn't have the same data using a different query. What I really want is a result where it shows data from both which is not the same. I haven't try using VIEW yet. I am very interested in how to make it work but I need to finish this job as fast as possible so if someone can give me an example, any example at all th
