select a.FID,a.FNUMBER,b.FNAME, FPartNum=isnull((select top 1 FNUMBER from T_BD_MATERIALGROUP as t with(nolock) where t.FID=a.FPARENTID),'') from T_BD_MATERIALGROUP as a with(nolock) inner join T_BD_MATERIALGROUP_L as b with(nolock) on b.FID = a.FID
select a.FID,a.FNUMBER,b.FNAME, FPartNum=isnull((select top 1 FNUMBER from T_BD_MATERIALGROUP as t with(nolock) where t.FID=a.FPARENTID),'') from T_BD_MATERIALGROUP as a with(nolock) inner join T_BD_MATERIALGROUP_L as b with(nolock) on b.FID = a.FID