如何解决Subquery返回的多个值。子查询时不允许这样做 [英] how to solve the Subquery returned more than 1 value. This is not permitted when the subquery

问题描述

嗨朋友



我的查询就像是，

hi friends

my query is like,

select  ER.BUSINESSNAME as ClientName,E.EMPLOYEE_FIRSTNAME+' '+E.EMPLOYEE_LASTNAME as EmployeeName,

        ISNULL((select TotalAmount from dbo.tblClientBenfitesForEmployee where  EmployerID=7 and SupportedID=1 ),0) as Advance,
         ISNULL((select TotalAmount from dbo.tblClientBenfitesForEmployee where   EmployerID=7 and SupportedID=2),0) as Child,
          ISNULL((select 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

i得到错误

i got the error

Msg 512, Level 16, State 1, Line 1
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

如何解决我的错误..请帮帮我并给我任何想法。

how to solve my error.. please help me and give me any ideas.

推荐答案

在子查询中应用限制0,1可以解决问题

Apply limit 0,1 in subquery may resolve the issue

tblClientBenfitesForEmployee 中 TotalAmount 的行数超过1行

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!
:)

这篇关于如何解决Subquery返回的多个值。子查询时不允许这样做的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！