多表关联查询的问题

[yycpp]

A表：id name
B表：id name
C表：idA1 idA2 idB(idA1、idA2来源于A表， idB来源于B表）
查询需得到的结果：
C.idA1 A.name(C.idA1在表A中的name） C.idA2 A.name（c.idA2在表A中的name） C.idB B.name（c.idB在表B中的name）

请问查询语句应该怎么写呢？谢谢

[suchabbs]

2楼和3楼都OK,哪个执行效率高点?请大牛们指点,随便帮顶+JF

[xiaomeixiang]

select C.idA1,A1.name,C.idA2,A2.name,C.idB,B.name
from C , A as a1, A as a2, B
where  c.idA1=a1.id and c.idA2=a2.id and c.idB=B.id

[xiaomeixiang]

A表关联两次

[dawugui]

select c.idA1,
       name = (select name from A where id = C.idA1),
       c.idA2,
       name = (select name from A where id = C.idA2),
       C.idB ,
       name = (select name from B where id = C.idB)
from C

[fcuandy]

SELECT c.ida1,a.name,c.ida2,d.name [a.name],c.idb,b.name
	FROM tc c
INNER JOIN ta a
	ON c.ida1=a.id
INNER JOIN tb b
	ON c.idb=b.id
INNER JOIN ta d
	ON c.ida2=d.id