急求：两个表关联的问题。。。

[yczyk]

A表和B表通过Field1和Field2关联，表结构相同，现在要得到这个一个数据集，它的记录在A表中存在但在B表中却不存在。要求用一条查询语句完成查询！

即：
A表：
Field1 Field2 value1 value2
A AB 1 1
B BC 1 1
C CD 1 1
B表
Field1 Field2 value1 value2
A AB 1 1
要得到的新表为：
Field1 Field2 value1 value2
B BC 1 1
C CD 1 1

[NinGoo]

select * from A
where field1 not in
(select field1 from B)

[zjcxc]

SELECT * FROM A表 a
where not exists( select * from B表 where Field1=a.Field1 and Field2=a.Field2)

[zjcxc]

--或者:

SELECT a.* FROM A表 a
left join(select distinct Field1,Field from B表)b on b.Field1=a.Field1 and b.Field2=a.Field2
where b.field1 is null and b.field2 is null

[子陌红尘]

select * from A where not exists(select 1 from B where Field1 = A.Field1 and Field2 = A.Field2 and value1 = A.value1 and value2 = A.value2)

[chinaandys]

select A.* from A表 a left join B表 b
on a.field1=b.field1
and a.field2=b.field2
and b.field1 is null

[jim138]

楼上的两个都OK...
UP :)))

[子陌红尘]

select
*
into
newTablename
from
A
where
not exists(select 1 from B where Field1 = A.Field1 and Field2 = A.Field2 and value1 = A.value1 and value2 = A.value2)

[jim138]

哦，太快了,是一楼和二楼的答案都OK

[NinGoo]

select * from A where not exists(select 1 from B where field1=A. field1)

[zengzhengliang]

select * from A a
where not exists(select * from B b where a.field1=b.field1 and a.field2=b.field2
and a.value1=b.value1 and a.value2=b.value2

[steven2003]

SELECT A.Field1,A.Field2,A.value1,A.value2
FROM A INNER JOIN
B ON A.Field1 <> B.Field1 AND A.Field2 <> B.Field2