higgsy higgsy - 1 year ago 73
SQL Question

Access join on first record

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

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!)

The query is 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

At the moment this returns results such as:

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

Is this possible in Access?

Thanks in advance

Answer Source

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