Nested Query with many-to-many relationship

ajarrell

Registered User.
Local time
Today, 19:15
Joined
Feb 4, 2014
Messages
56
I have a db of contacts that includes a linking table to match contacts to the groups each is in. I need to be able to query for contacts that are in a specified group AND in another specified group. My query instead returns all contacts in [SelectGroup2]. Is there a way to make this work? Thanks in advance!

SELECT tblContacts.[FirstName], tblContacts.[LastName], tblContacts.Company, tblContacts.Email, tblContacts.Type, tblGroups.Group

FROM tblGroups INNER JOIN (tblContacts INNER JOIN tblContGrpLink ON tblContacts.ContactID = tblContGrpLink.Contact) ON tblGroups.GroupID = tblContGrpLink.Group

WHERE (((tblGroups.Group)=[Select Group 2])) AND EXISTS
( SELECT tblGroups.Group
FROM tblGroups AS GroupList INNER JOIN (tblContacts INNER JOIN tblContGrpLink ON tblContacts.ContactID = tblContGrpLink.Contact) ON GroupList.GroupID = tblContGrpLink.Group
WHERE (((GroupList.Group)=[SelectGroup1])));
 
I need to be able to query for contacts that are in a specified group AND in another specified group

Going off that, I would use a Summation query. You would limit it to the 2 groups you wanted and then return results that have a total of 2 records. Like so:

Code:
SELECT ContactID
FROM tblGroups 
WHERE Group="Group 1" OR Group="Group 2"
GROUP BY ContactID
HAVING COUNT(ContactID)=2

The only issue with this is if someone could have 2 records for the same group. In that case you would need a subquery to determine the unique groups a person is in.
 
Thanks for your timely response!

I have not allowed records in the link table to have the same person and group more than once.

Now I am getting the message: "Your query does not include the specified expression 'First Name' as part of an aggregate function."

Thanks, again.
 
What does your SQL look like? Sounds to me like you have fields in the SELECT that are not in the GROUP BY
 
SELECT tblContacts.[ContactID],tblContacts.[First Name], tblContacts.[Last Name], tblContacts.[Company], tblContacts., tblGroups.Group
FROM tblGroups INNER JOIN (tblContacts INNER JOIN tblContGrpLink ON tblContacts.ContactID = tblContGrpLink.Contact) ON tblGroups.GroupID = tblContGrpLink.Group
WHERE (((tblGroups.Group)=[Select Group 1])) OR (((tblGroups.Group)=[Select Group 2]))

GROUP BY ContactID HAVING COUNT(ContactID)=2;
 
When you have a GROUP BY clause, every item in the SELECT must either be in the GROUP BY or be acted on by a aggregate function (SUM, COUNT, MAX, etc.). So put every field in the SELECT in the GROUP BY
 
That fixed the error.

SELECT tblContacts.[ContactID],tblContacts.[First Name], tblContacts.[Last Name], tblContacts.[Company], tblContacts., tblGroups.Group
FROM tblGroups INNER JOIN (tblContacts INNER JOIN tblContGrpLink ON tblContacts.ContactID = tblContGrpLink.Contact) ON tblGroups.GroupID = tblContGrpLink.Group
WHERE (((tblGroups.Group)=[Select Group 1])) OR (((tblGroups.Group)=[Select Group 2]))

GROUP BY tblContacts.[ContactID],tblContacts.[First Name], tblContacts.[Last Name], tblContacts.[Company], tblContacts.[Email], tblGroups.Group
HAVING COUNT(ContactID)=2;


However, now I get no records. If I leave off the HAVING COUNT(ContactID)=2, I get the appropriate contacts, some twice as expected.
 
In the posting box at the bottom, go to 'Go Advanced'. One of the options will be to upload a database.

I'd prefer a slimmed down version--just the tables you are working with--no forms, no reports, etc. Make it easy for me to find what to work with.
 
You can't GROUP BY the Group field to get the results you want. Remove that from both the SELECT and the GROUP BY clauses.
 

Users who are viewing this thread

Back
Top Bottom