访问第一条记录上的联接 [英] Access join on first record

问题描述

我在Access数据库中有两个表tblProducts和tblProductGroups.

I have two tables in an Access database, tblProducts and tblProductGroups.

我正在尝试运行一个将两个表都连接在一起的查询，并为每个产品带回一条记录.问题在于，当前的设计允许产品在tblProductGroups表中列出的数目超过1-即产品可以是多个组的成员(我没有设计这个！)

I am trying to run a query that joins both of these tables, and brings back a single record for each product. The problem is that the current design allows for a product to be listed in the tblProductGroups table more than 1 - i.e. a product can be a member of more than one group (i didnt design this!)

查询是这样的:

select tblProducts.intID, tblProducts.strTitle, tblProductGroups.intGroup 
from tblProducts 
inner join tblProductGroups on tblProducts.intID = tblProductGroups.intProduct 
where tblProductGroups.intGroup = 56 
and tblProducts.blnActive 
order by tblProducts.intSort asc, tblProducts.curPrice asc

此刻返回的结果如下:

intID | strTitle | intGroup
1     | Product 1 | 1
1     | Product 1 | 2
2     | Product 2 | 1
2     | Product 2 | 2

我只希望联接基于第一个匹配的记录，因此它将返回:

Whereas I only want the join to be based on the first matching record, so that would return:

intID | strTitle | intGroup
1     | Product 1 | 1
2     | Product 2 | 1

在Access中有可能吗?

Is this possible in Access?

先谢谢了 铝

推荐答案

此选项运行子查询以查找每个tblProducts.intID的最小intGoup.

This option runs a subquery to find the minimum intGoup for each tblProducts.intID.

SELECT tblProducts.intID
, tblProducts.strTitle
, (SELECT TOP 1 intGroup 
   FROM tblProductGroups 
   WHERE intProduct=tblProducts.intID 
   ORDER BY intGroup ASC) AS intGroup
FROM tblProducts 
WHERE tblProducts.blnActive 
ORDER BY tblProducts.intSort ASC, tblProducts.curPrice ASC

这篇关于访问第一条记录上的联接的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！