CODEWITHSUNDEEP
ms-accesssubquery
mrsw
mrsw

Reputation: 175

Creating a subquery in Access

I am attempting to create a subquery in Access but I am receiving an error stating that one record can be returned by this subquery. I am wanting to find the top 10 companies that have the most pets then I want to know the name of those pets. I have never created a subquery before so I am not sure where I am going wrong. Here is what I have: 

SELECT TOP 10 dbo_tGovenrnmentRegulatoryAgency.GovernmentRegulatoryAgency
(SELECT dbo_tPet.Pet
FROM dbo_tPet)

FROM dbo_tPet INNER JOIN dbo_tGovenrnmentRegulatoryAgency ON
dbo_tPet.GovernmentRegulatoryAgencyID =
dbo_tGovenrnmentRegulatoryAgency.GovernmentRegulatoryAgencyID
GROUP BY dbo_tGovenrnmentRegulatoryAgency.GovernmentRegulatoryAgency
ORDER BY Count(dbo_tPet.PetID) DESC;

Upvotes: 1

Views: 132

Answers (2)

Parfait
Parfait

Reputation: 107567

Consider this solution, requiring a subquery in the WHERE IN () clause:

SELECT t1.GovernmentRegulatoryAgency, dbo_tPet.Pet,
FROM dbo_tPet 
INNER JOIN dbo_tGovenrnmentRegulatoryAgency t1 ON
dbo_tPet.GovernmentRegulatoryAgencyID = t1.GovernmentRegulatoryAgencyID    
WHERE t1.GovernmentRegulatoryAgency IN   
    (SELECT TOP 10 t2.GovernmentRegulatoryAgency
    FROM dbo_tPet 
    INNER JOIN dbo_tGovenrnmentRegulatoryAgency t2 ON
    dbo_tPet.GovernmentRegulatoryAgencyID = t2.GovernmentRegulatoryAgencyID
    GROUP BY t2.GovernmentRegulatoryAgency
    ORDER BY Count(dbo_tPet.Pet) DESC);

Table aliases are not needed but I include them for demonstration.

Upvotes: 1

Chris Rolliston
Chris Rolliston

Reputation: 4808

This should hopefully do it:

SELECT a.GovernmentRegulatoryAgency, t.NumOfPets
FROM dbo_tGovenrnmentRegulatoryAgency a
INNER JOIN (
    SELECT TOP 10 p.GovernmentRegulatoryAgencyID, COUNT(p.PetID) AS NumOfPets
    FROM dbo_tPet p
    GROUP BY p.GovernmentRegulatoryAgencyID
    ORDER BY COUNT(p.PetID) DESC
) t
ON a.GovernmentRegulatoryAgencyID = t.GovernmentRegulatoryAgencyID

In a nutshell, first get the nested query sorted, identifying what the relevant agencies are, then inner join back to the agency table to get the detail of the agencies so picked.

Upvotes: 0

Related Questions