全连接问题

[aist0217]

table1:
a b
111 10
112 20
113 50


table2:
a b
211 10
212 20
213 30
214 20


table3:
a1 a2
111 211
112 212
113 213
113 214
... ...
... ...



table3 的a1列存有table1的a列 a2列存有table2的a列

我想查询出：
a1 a2
111 211
112 212
113 213
113 214

请问如何实现？

[soft_wsx]

distinct!

[dawugui]

???

不是第三表?

[chuifengde]

select * from tb3 aa 
where exists(select 1 from tb1 where a=aa.a1) 
and exists(select 1 from tb2 where a=aa.a2)

[华夏小卒]

select c.* from table3 c,table1 a,table2 b
where a.a=c.a1 and b.a=c.a2

??

[gsk09]

select a1,a2 from table3 

[--小F--]

select c.a1,c.a2 from c join a on c.a1=a.a join b on c.a2=b.a

[--小F--]

select c.a1,c.a2 from table3 c join table1 a on c.a1=a.a join table2 b on c.a2=b.a

[aist0217]

[Quote=引用 7 楼 fredrickhu 的回复:]
SQL codeselect c.a1,c.a2from table3 cjoin table1 aon c.a1=a.ajoin table2 bon c.a2=b.a
[/Quote]


这样我试过了 ，只能出三行 也就是
111 211
112 212
113 213

a1列113两行却只能出一行 是为什么呢？

[qhl77711]

连接查询

[dawugui]

create table table1(a int, b int)
insert into table1 values(111 , 10 )
insert into table1 values(112 , 20 )
insert into table1 values(113 , 50 )
create table table2(a int, b int)
insert into table2 values(211 , 10 )
insert into table2 values(212 , 20 )
insert into table2 values(213 , 30 )
insert into table2 values(214 , 20 )
create table table3(a1 int, a2 int)
insert into table3 values(111 , 211 )
insert into table3 values(112 , 212 )
insert into table3 values(113 , 213 )
insert into table3 values(113 , 214 )
go

select t3.* from table3 t3
where exists(select 1 from table1 t1 where t1.a = t3.a1) and exists(select 1 from table2 t2 where t2.a = t3.a2)

drop table table1,table2,table3

/*
a1 a2
----------- -----------
111 211
112 212
113 213
113 214

（所影响的行数为 4 行）

*/