返回多个字段的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个字段是我的酒吧"数据库中的组合键.

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 Access2007.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值:

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屋！