select A.codea,A.codeb,B.name as nameA,C.name as nameB
from table1 as A
left join table2 as B on A.codea=B.code
left join table2 as C on A.codeb=C.code

------解决方案--------------------

SQL code

--> 测试数据：[table1]
if object_id('[table1]') is not null drop table [table1]
create table [table1]([codea] int,[codeb] int)
insert [table1]
select 8,2 union all
select 3,1 union all
select 5,3 union all
select 2,7
--> 测试数据：[table2]
if object_id('[table2]') is not null drop table [table2]
create table [table2]([code] int,[name] varchar(6))
insert [table2]
select 1,'tom' union all
select 2,'kate' union all
select 3,'sherry' union all
select 4,'tracy'

select 
a.*,isnull(b.name,'') as nameA,
isnull(c.name,'') as nameB
from table1 a
left join table2 b on a.codea=b.code
left join table2 c on a.codeb=c.code
/*
codea    codeb    nameA    nameB
---------------------------------------------
8    2        kate
3    1    sherry    tom
5    3        sherry
2    7    kate    
*/