Subquery returned more than 1 value. This is not permitted

Question

i have a table which contains the following fields

• Supervisorid
• Empid

This is just like a referral program. A guy can refer 3 guys under him i.e, 3 is referring three guys namely 4 5 8 similarly 4 is referring 9 10 and 11 likewise 8 is referring 12, 13 it goes like this..

I want a query to get all EmpId under a guy say 3

Below is my code, and its give a error, because variable hold only ine value so give me solution.

Declare @SupervisorId integer
Declare @EmpId int
Declare @temp varchar(100)
Select @SupervisorId=(Select count(*) from EmployeeMaster)
Set @temp=52
Declare @Table2 table(EmpId int)
while @SupervisorId >0
begin

Select @temp=(Select EmpId from EmployeeMaster where SupervisorId=@temp)

set @SupervisorId=@SupervisorId - 1
insert into @Table2(EmpId)
Select EmpId from EmployeeMaster where EmpId=@temp
End
Select Distinct * from @Table2

Answer 1

You need to use a recursive CTE.

Here's a simple example of one that you can tweak to fit your situation.

By the way: What is the purpose of this bit of code? Select @SupervisorId=(Select count(*) from EmployeeMaster)