Yahoo Answers is shutting down on May 4th, 2021 (Eastern Time) and beginning April 20th, 2021 (Eastern Time) the Yahoo Answers website will be in read-only mode. There will be no changes to other Yahoo properties or services, or your Yahoo account. You can find more information about the Yahoo Answers shutdown and how to download your data on this help page.
Trending News
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
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
*at least ONE column which contain the same data :P
@Serge M; That doesn't work either.
3 Answers
- 9 years agoFavorite 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
- Unca AlbyLv 79 years ago
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.
- Serge MLv 69 years ago
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');