Subquery returns more than 1 row

Question

Hi I have a mysql query to fetch a number of id's(studentID) which as given below: select t1.studentID from remittedfees t1 where (select t2.fees from feesasigned t2 where t2.studentID=t1.studentID)=(select sum(t3.remittedAmt) from remittedfees t3 where t3.studentID=t1.studentID); but the query returns the following error ERROR 1242 (21000): Subquery returns more than 1 row. Thanks

Arjun Panwar · Answer

Thanks to all.

Senthilkumar · Answer

Hi Arjun When ever you write the select sub query then you have to keep one thing in your mind. SELECT * FROM Employee WHERE eid = (SELECT employeeid FROM salary) -- Error when returns more than 1 result. SELECT * FROM Employee WHERE eid = (SELECT TOP 1 employeeid FROM salary) -- It will not give error. Here what happend when salary sub query returns more than one result then it will be an issue. Because = will check only single value not a table. The best practices that case you write like this. SELECT * FROM Employee WHERE eid IN (SELECT employeeid FROM salary) USe the same logic to your query. If it is useful then mark it as Accepted Answer

chirag pandya · Answer

Hi Arjun you have done only 1 mistake you forgot to put sum keyword in first where filter i have rectified you query now its running successfully its is as below select t1.studentID from remittedfees t1 where (select sum(t2.fees) from feesasigned t2 where t2.studentID=t1.studentID)=(select sum(t3.remittedAmt) from remittedfees t3 where t3.studentID=t1.studentID);

Shen Hengbin · Answer

you can't write like that. I don't know if I understand your mean  can you check the below code ? select distinct t1.studentID from ( select studentID  sum(remittedAmt) as amt from remittedfees group by studentID ) t1 left join feesasigned t2 on t1.studentID=t2.studentID and t1.amt = t2.fees select t1.studentID  t2.fees from remittedfees t1 left join feesasigned t2 on t1.studentID=t2.studentID and sum(t1.remittedAmt) = t2.fees