select MgrID, EmpID, EmployeeName
 from emp e
where MgrID in (select EmpID from emp b 
	          where b.EmpID = 1)

declare @child_param int
set @child_param = 3

select a.* from #temp1 a 
inner join
(select distinct MID from #temp1
where EID = @child_param)as b
on a.Mid= B.MID

--=======================================================================================
--      Setup some test data... note that nothing in this section is part of the actual
--      solution.
--=======================================================================================

--===== Setup a "quiet" environment
    SET NOCOUNT ON

--===== Create a table to hold some test data.  
     -- This is NOT part of the solution
 CREATE TABLE #yourtable 
        (
        ID       INT,
        ParentID INT,
        Descrip  VARCHAR(20)
        )

--===== Populate the test table with 2 "trees" of data
 INSERT INTO #yourtable
        (ID,ParentID,Descrip)
 SELECT 9,NULL,'County 1'     UNION ALL --Note NULL, this is top node of "Tree 1"
 SELECT 2,9   ,'C1 Region 1'  UNION ALL
 SELECT 4,9   ,'C1 Region 2'  UNION ALL
 SELECT 3,2   ,'C1 R1 Unit 1' UNION ALL
 SELECT 5,2   ,'C1 R1 Unit 2' UNION ALL
 SELECT 6,4   ,'C1 R2 Unit 1' UNION ALL
 SELECT 7,NULL,'County 2'     UNION ALL --Note NULL, this is top node of "Tree 2"
 SELECT 8,7   ,'C2 Region 1'  UNION ALL
 SELECT 1,9   ,'C1 Region 3'

--=======================================================================================
--      The following code makes a Hierarchy "sister" table with strings that are used
--      to traverse various hierarchies.
--=======================================================================================
--===== Create and seed the "Hierarchy" table on the fly
 SELECT ID, 
        ParentID, 
        Descrip, 
        Level = 0, --Top Level
        HierarchyString = CAST(STR(ID,5) AS VARCHAR(8000))+' '
   INTO #Hierarchy
   FROM #yourtable
  WHERE ParentID IS NULL

--===== Declare a local variable to keep track of the current level
DECLARE @Level INT
    SET @Level = 0

--===== Create the hierarchy in the HierarchyString
  WHILE @@ROWCOUNT > 0 
  BEGIN
            SET @Level = @Level + 1

         INSERT INTO #Hierarchy
                (ID, ParentID, Descrip, Level, HierarchyString)
         SELECT y.ID,y.ParentID,y.Descrip, @Level, h.HierarchyString + STR(y.ID,5) + ' '
           FROM #yourtable y
          INNER JOIN #Hierarchy h
             ON y.ParentID = h.ID       --Looks for parents only
            AND h.Level    = @Level - 1 --Looks for parents only
    END

--=======================================================================================
--      Now, demo the use of the sister table
--=======================================================================================
--===== Display the entire tree with indented descriptions according to the Level
 SELECT ID, 
        ParentID, 
        Level,
        LEFT(REPLICATE(' ',Level*2)+descrip,30),
        HierarchyString 
   FROM #Hierarchy
  ORDER BY HierarchyString

--===== Select only the "downline" for ID 2 including ID 2
 SELECT ID, 
        ParentID, 
        Level,
        LEFT(REPLICATE(' ',Level*2)+descrip,30),
        HierarchyString 
   FROM #Hierarchy
  WHERE HierarchyString LIKE '% 2 %'
  ORDER BY HierarchyString

drop table #Hierarchy
drop table #yourtable