left join
select (select name from tb where id=id1) as id1
from tb2
select a.name,b.name,c.name from tb t
left join ta a on t.id1=a.id
left join ta b on t.id2=b.id
left join ta b on t.id3=c.id
--> 测试数据: #T1
if object_id('tempdb.dbo.#T1') is not null drop table #T1
create table #T1 (id int,name varchar(4))
insert into #T1
select 1,'小张' union all
select 2,'小李' union all
select 3,'小陈'
--> 测试数据: #T2
if object_id('tempdb.dbo.#T2') is not null drop table #T2
create table #T2 (id1 int,id2 int,id3 int)
insert into #T2
select 1,3,2 union all
select 3,2,2
select t1.name n1,t2.name n2,t3.name n3 from #T2 t
left join #t1 t1 on t1.id=t.id1
left join #t1 t2 on t2.id=t.id2
left join #t1 t3 on t3.id=t.id3
n1 n2 n3
---- ---- ----
小张 小陈 小李
小陈 小李 小李
(2 行受影响)
create table a (id int,name varchar(4))
insert into a
select 1,'小张' union all
select 2,'小李' union all
select 3,'小陈'
create table b (id1 int,id2 int,id3 int)
insert into b
select 1,3,2 union all
select 3,2,2
go
select t1.name,
t2.name,
t3.name
from b
inner join a t1 on b.id1 = t1.id
inner join a t2 on b.id2 = t2.id
inner join a t3 on b.id3 = t3.id
drop table a , b
/*
name name name
---- ---- ----
小张 小陈 小李
小陈 小李 小李
（所影响的行数为 2 行）
*/
