you are getting more than 1 rows for
TotalAmount in
tblClientBenfitesForEmployee
select ER.BUSINESSNAME as ClientName,E.EMPLOYEE_FIRSTNAME+' '+E.EMPLOYEE_LASTNAME as EmployeeName,
ISNULL((select Top 1 TotalAmount from dbo.tblClientBenfitesForEmployee where EmployerID=7 and SupportedID=1 ),0) as Advance,
ISNULL((select Top 1 TotalAmount from dbo.tblClientBenfitesForEmployee where EmployerID=7 and SupportedID=2),0) as Child,
ISNULL((select Top 1 TotalAmount from dbo.tblClientBenfitesForEmployee where EmployerID=7 and SupportedID=3) ,0)as Health
from dbo.tblAssignEmployeesToClients AEC
inner join dbo.EMPLOYEE_DETAILS E on AEC.EmployeeID=E.EMPLOYEE_ID
inner join dbo.EMPLOYER_REGISTRATION ER on AEC.EmployerID=ER.EMPLOYER_ID
inner join dbo.tblClientBenfitesForEmployee CBF on AEC.EmployerID=CBF.EmployerID
inner join dbo.tblClientBenfitesList CB on CBF.SupportedID=CB.SupportID
where AEC.EmployerID=7
Happy Coding!
:)