Subquery returns more than 1 row

Thanks to all.

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"

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);

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