帮忙看下，我这里为什么实行不了，谢谢

Thomas2800

SELECT 工服.姓名   from  工服
intersect
SELECT 工鞋.姓名  from  工鞋;
为什么实行不了， 我是在access下的sql 里面的

zhaozyuan

hangglide05-11-2005, 02:39 PM
Sorry if this has already been answered. I searched and couldn't find it.

How do I do an INTERSECT query in Access? It is my understanding that Access does not support the INTERSECT query. Is this right?

The syntax for an INTERSECT query is:

select field1, field2, … field_n
from tables
INTERSECT
select field1, field2, … field_n
from tables;


Example #1

The following is an example of an INTERSECT query:

select supplier_id
from suppliers
INTERSECT
select supplier_id
from orders;


The INTERSECT query allows you to return the results of 2 or more "select" queries. However, it only returns the rows selected by all queries. If a record exists in one query and not in the other, it will be omitted from the INTERSECT results.

Is there a simple way to do that in Access? The only work around I know is to run the two select queries seperate and then run a third to see what they have in common. There has to be a simpler method (I want to code the solution in VBA script for an application I am writing).

Leo

zhaozyuan

Try this (off the top of my head, but I think it will work):

SELECT suppliers.supplier_id
FROM suppliers INNER JOIN orders ON suppliers.supplier_id = orders.supplier_id;

zhaozyuan

在access里没有intersect,要用join
SELECT 工服.姓名 from  工服 left join 工鞋 on 工鞋.姓名=工服.姓名 where 工鞋.姓名 is null
不好意思，这个是subtract不是intersect,请看楼下

[ 本帖最后由 zhaozyuan 于 2009-12-25 09:52 编辑 ]

Thomas2800

谢谢 ，太感谢了 ，大哥 你的英语真好 ，佩服   我要学英语 。。。。。。。。。

Thomas2800

试了下，说不支持 join

可以了，用 join 后 返回的好像是表格1里面有而表格2里面没有的， 有没有办法返回两个表格共有的  ，谢谢

[ 本帖最后由 Thomas2800 于 2009-12-25 09:25 编辑 ]

zhaozyuan

原帖由 Thomas2800 于 2009-12-25 09:22 发表
试了下，说不支持 join

可以了，用 join 后 返回的好像是表格1里面有而表格2里面没有的， 有没有办法返回两个表格共有的  ，谢谢

哈哈,我搞错了,不好意思阿
SELECT 工服.姓名 from  工服 inner join 工鞋 on 工鞋.姓名=工服.姓名

Thomas2800

非常感谢 ，谢谢

Thomas2800

为什么显示出来有重复的姓名呢，谢谢

zhaozyuan

可能你的源表有重复
你可以试下
SELECT distinct 工服.姓名 from  工服 inner join 工鞋 on 工鞋.姓名=工服.姓名