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.

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

Update:

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

Update 2:

*at least ONE column which contain the same data :P

Update 3:

@Serge M; That doesn't work either.

3 Answers

Relevance
  • 9 years ago
    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

  • 9 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.

  • 9 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');

Still have questions? Get your answers by asking now.