英文:
How can I restrict results in an Access SELECT Query
问题
I'm working in Access, querying an assignment table between two tables having a many-to-many relationship. Values from the external Data table are collected together into a group, and that group is given an identifying value from the external ID table. In the target table, the ID value repeats only for each Data item in the group. The Data values may repeat, but never under the same ID value.
IDVal DataVal
- - - - - - -
A 100
A 200
A 300
A 400
- - - - - - -
B 200
B 300
B 400
B 500
- - - - - - -
C 100
C 200
C 300
C 400
C 500
From another process, I have an incoming set of Data values (say, 200, 300, 400, 500). I'm trying to write a single query against this table that returns the ID associated with only those incoming values. If I write a regular SELECT query, then I'll get back both B and C as my ID values. How can I make my query return only the ID identifying exactly the data set I'm looking for? I'm not yet proficient enough with SQL to manage this with a single query.
英文:
I'm working in Access, querying an assignment table between two tables having a many-to-many relationship. Values from the external Data table are collected together into a group, and that group is given an identifying value from the external ID table. In the target table, the ID value repeats only for each Data item in the group. The Data values may repeat, but never under the same ID value.
IDVal DataVal
- - - - - -
A 100
A 200
A 300
A 400
- - - - - -
B 200
B 300
B 400
B 500
- - - - - -
C 100
C 200
C 300
C 400
C 500
From another process, I have an incoming set of Data values (say, 200, 300, 400, 500). I'm trying to write a single query against this table that returns the ID associated with only those incoming values.
If I write a regular SELECT query, then I'll get back both B and C as my ID values.</p> How I can make my query return only the ID identifying exactly the data set I'm looking for? I'm not yet proficient enough with SQL to manage this with with a single query.
答案1
得分: 1
尝试以下查询(可能由查询构造器创建)
SELECT Data.IdVal, Count(Data.DataVal) AS [Count-DataVal]
,Count(Test.Data) AS [Count-Data1]
,Sum(IIf(IsNull([test].[data]),1,0)) AS sNN
FROM Test RIGHT JOIN Data ON Test.Data = Data.DataVal
GROUP BY Data.Id
HAVING (((Sum(IIf(IsNull([test].[data]),1,0)))=0));
结果
IdVal | Count-DataVal | Count-Data1 | sNN |
---|---|---|---|
B | 4 | 4 | 0 |
不使用HAVING筛选条件
Id | Count-DataVal | Count-Data1 | sNN |
---|---|---|---|
A | 4 | 3 | 1 |
B | 4 | 4 | 0 |
C | 5 | 4 | 1 |
Test
表 - 输入数据
Data |
---|
200 |
300 |
400 |
500 |
英文:
Try this query (possible create by Query constructor)
SELECT Data.IdVal, Count(Data.DataVal) AS [Count-DataVal]
,Count(Test.Data) AS [Count-Data1]
,Sum(IIf(IsNull([test].[data]),1,0)) AS sNN
FROM Test RIGHT JOIN Data ON Test.Data = Data.DataVal
GROUP BY Data.Id
HAVING (((Sum(IIf(IsNull([test].[data]),1,0)))=0));
Result
IdVal | Count-DataVal | Count-Data1 | sNN |
---|---|---|---|
B | 4 | 4 | 0 |
Without filter by HAVING
Id | Count-DataVal | Count-Data1 | sNN |
---|---|---|---|
A | 4 | 3 | 1 |
B | 4 | 4 | 0 |
C | 5 | 4 | 1 |
Test table Test
- incoming data
Data |
---|
200 |
300 |
400 |
500 |
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论