create table T1(ID int, code int, name varchar(10), companycode varchar(10))
insert T1 select 1, 1, '张三', '1'
union all select 2, 2, '李四', '2'
union all select 3, 1, '张三', '10001'
union all select 4, 2, '李四', '10002'
go
create table T2(companycode int, companyname varchar(10))
insert T2 select 1, '联想'
union all select 2, 'IBM'
go
create table T3(companycode varchar(10), companyname varchar(10))
insert T3 select '10001', '联想'
union all select '10002', 'IBM'
select T1.ID, T1.code, T1.name,
companyname=isnull(T2.companyname, T3.companyname)
from T1
left join T2 on T1.companycode=T2.companycode
left join T3 on T1.companycode=T3.companycode
--result
ID code name companyname
----------- ----------- ---------- -----------
1 1 张三 联想
2 2 李四 IBM
3 1 张三 联想
4 2 李四 IBM