SOQL Query with three objects fails

Question

I am quite new to SOQL and ran into a problem with my query in SOQL Query on Workbench.

My goal: Identifying all contacts which are in campaign "Hello" but NOT in campaign "Ciao".

I have three objects: Contact, CampaignMember, Campaign. CampaignMember is the bridge between Contact and Campaign with a LookUp in both directions.

My take was this query:

SELECT Contact.FirstName, Contact.LastName, ContactId, contact.AccountId FROM CampaignMember WHERE Campaign.Name = 'Hello' AND Campaign.Name != 'Ciao'

I don't know why, but the result is just a list of contacts belonging to Campaign.Name 'Hello'. My query completely ignores the difference in contacts between the "Hello" and "Ciao" campaign. Even though I know for certain that there are contacts which are in campaign "Hello" and not in campaign "Ciao".

I'd appreciate any feedback. Thanks in advance!

Answer 1

You need to use two semi-joins to achieve this use case, and query from the Contact level. This allows you to filter by membership in the junction object CampaignMember:

SELECT FirstName, LastName, Id, AccountId 
FROM Contact
WHERE Id IN (SELECT ContactId FROM CampaignMember WHERE Campaign.Name = 'Hello')
AND Id NOT IN (SELECT ContactId FROM CampaignMember WHERE Campaign.Name = 'Ciao')

That would yield Contacts that DO have a CampaignMember in the Campaign 'Hello', and DO NOT have a CampaignMember in the Campaign 'Ciao'.

Learn more about semi-join and anti-join patterns in the SOQL and SOSL Reference.