返回多个字段的 MS Access 子查询 [英] MS Access Subquery that returns Multiple Fields

问题描述

所以我又回来了，遇到了更多的 MS Access 问题.我有一个带有子查询的 INSERT INTO 查询，用于检查数据是否已经存在.

So im back again with more MS Access problems. I have a INSERT INTO query with a subquery that checks to see if the data already exists.

SELECT name, course
FROM foo    
WHERE (name, course) NOT IN (SELECT name, course FROM bar);

解释一下我想要完成的事情，因为上述方法不起作用.

to expound a little on what i am trying to accomplish since the above doesn't work.

我正在尝试选择表格栏中尚不存在的复合键.例如，可以在表格栏中存储以下内容:

I am trying to select composite keys that do not exist already in the table bar. For example the following could be stored in the table bar:

"John Doe" , "Calc 101" 
"John Doe" , "English"
"Jane Doe" , "Calc 101"

以下可能在表 foo 中:

And the following could be in the table foo:

"John Doe", "Calc 101"
"John Doe", "Science"

查询应返回以下内容:

"John Doe", "Science"

我看过的所有地方都说上面的方法会起作用，我相信它在理论上是有效的.我遇到的问题是 MS Access... 当我尝试运行此查询时，它会弹出说明子查询将返回多个字段.事实上，它应该是我想要它做的就是返回 2 个字段，我可以比较其他 2 个字段.以上 2 个字段是我的bar"数据库中的复合键.

Everywhere i have looked says the above will work and im sure it does in theory. The problem i run into is with MS Access... When i attempt to run this query it pops up stating that the subquery will return multiple fields. Indeed it should as that is what i want it do do is return 2 fields that i can compare the other 2 fields. The above 2 fields are a composite key in my "bar" database.

有关更多背景信息，我使用的是 MS Excel 2007 和 MS Access 2007.Excel 用于输入数据并通过 VB 脚本运行查询.我正在尝试创建一个子查询来检查最终数据库中已经存在的字段，因为我遇到了打开 MS Access 的错误，并吐出一条关于附加主键的错误消息，并在不执行查询的情况下关闭.<-- 由于复合键，这是可以预料的.

For more background I am using MS Excel 2007 and MS Access 2007. The Excel is being used to input data and running the query through VB script. I am trying to make a subquery that checks for fields already in the final database because i ran into the error of MS Access opening up and spitting out a ERROR message about appending primary keys and closes with out executing the query. <-- That is to be expected due to the composite key.

推荐答案

使用 LEFT JOIN 并查找 NULL 值:

Use LEFT JOIN and look for NULL values:

SELECT bar.name, bar.course
FROM bar LEFT JOIN foo ON bar.name = foo.name AND bar.course = foo.course
WHERE foo.name IS NULL

我已更新 SQLFiddle 以包含 INSERT 后跟 SELECT 以显示最终表格.我还在两个表中添加了复合主键，这样您就可以看到没有任何重复的插入.

I've updated the SQLFiddle to include the INSERT followed by a SELECT to show the final table. I've also added composite primary keys to both tables so you can see that you do not get any duplicate inserts.

这篇关于返回多个字段的 MS Access 子查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！