Question

查询一个表中所有id字段在另一个表中对应值的SQL语句怎么写？

求SQL查询语句，查询出表TB中所有id在TA对应的name值，表记录举例如下：

表名：TA
id name
-------
1 A
2 B
3 C

表名：TB
id1 id2
-------
1 2
2 1
2 3

查询结果举例：
name1 name2
A B
B A
B C
谢谢各位的热心回答，今天很晚了，明天我再一一验证并学习揣摩各个答案。

Answer 1

子查询即可
SELECT (SELECT name FROM TA WHERE id = TB.id1) AS id1,
(SELECT name FROM TA WHERE id = TB.id2) AS id2
FROM TB

Answer 2

select c.name,d.name from (select a.id1 id1,a.id2 id2,a.name name from TA a,TB b where a.id1=b.id1) temp c
,TA d where c.id2=d.id2

Answer 3

SELECT T1.name as name1,T2.name as name2 FROM TA as T1,TA as T2,TB
WHERE T1.id = TB.id1 AND T2.id = TB.id2

追问

速度真快！！

追答

呵呵，工作之余上来帮帮忙呗

Answer 4

--创建测试数据
create table #a(id int,name varchar(10))
insert into #a values(1,'A')
insert into #a values(2,'B')
insert into #a values(3,'C')

create table #b(id1 int,id2 int)
insert into #b values(1,2)
insert into #b values(2,1)
insert into #b values(2,3)

---------------实现代码部分----------------
select
t1.name as name1
,t2.name as name2
from
#b tt
left join #a t1 on tt.id1 =t1.id
left join #a t2 on tt.id2 =t2.id

Answer 5

select * from ta,tb where ta.id= tb.id1