Hi,

try this, on inner join

create table #temp1

(

MID int,

EID int,

ENAME varchar(10)

)

insert into #temp1

select 1,1,'A'

union all

select 1,2,'AA'

union all

select 1,3,'AAA'

union all

select 1,4,'AAAA'

union all

select 2,1,'B'

union all

select 2,2,'BB'

union all

select 3,1,'C'

union all

select 3,2,'CC'

union all

select 3,3,'CCC'

select * from #temp1

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