这个嵌套查询应该怎么做？

[ltcszk]

a表
id name
1 aa
2 bb
3 cc
4 dd

b表（x、y、z是a表的id）
id x y z
1 4 2 3
2 1 2 3

现在想查出根据b表id查出xyz里对应a表的行

[叶子]

declare @a表 table (id int,name varchar(2))
insert into @a表
select 1,'aa' union all
select 2,'bb' union all
select 3,'cc' union all
select 4,'dd'

declare @b表 table (id int,x int,y int,z int)
insert into @b表
select 1,4,2,3 union all
select 2,1,2,3

select b.id,x=a1.name,y=a2.name,z=a3.name from @b表 b
,@a表 a1,@a表 a2,@a表 a3 
where b.x=a1.id and b.y=a2.id and b.z=a3.id
/*
id          x    y    z
----------- ---- ---- ----
1           dd   bb   cc
2           aa   bb   cc
*/

[jiao3630]

select id,x=(select name from a where a.id=x),
y=(select name from a where a.id=y),
z=(select name from a where a.id=z),
 from b

[ltcszk]

[Quote=引用 1 楼 maco_wang 的回复:]

SQL code

declare @a表 table (id int,name varchar(2))
insert into @a表
select 1,'aa' union all
select 2,'bb' union all
select 3,'cc' union all
select 4,'dd'

declare @b表 table (id int,x int,y int,z in……
[/Quote]

最后要的是a表的行
比如b表id=2 结果应该是
id name
1 aa
2 bb
3 cc
3

[mustudent]

[Quote=引用 3 楼 ltcszk 的回复:]

引用 1 楼 maco_wang 的回复:

SQL code

declare @a表 table (id int,name varchar(2))
insert into @a表
select 1,'aa' union all
select 2,'bb' union all
select 3,'cc' union all
select 4,'dd'

declare @……
[/Quote]

-你难道要这样的结果?

declare @a table (id int,name varchar(2))
insert into @a
select 1,'aa' union all
select 2,'bb' union all
select 3,'cc' union all
select 4,'dd'

declare @b table (id int,x int,y int,z int)
insert into @b
select 1,4,2,3 union all
select 2,1,2,3

Select b.id,a.* From @a a Inner Join @b b on a.id =b.x or a.id=b.y or a.id=b.z