0

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!

0

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.

4
  • Thanks you very much, David! I can relate to your query! I think it will do the tick but in my CampaignMember Object there is no CampaignMemberId field. RecordTypeId would be close to a CampaignMemberID but gives me this error message: The selected field 'RecordTypeId' in the subquery and the left operand field in the where expression in the outer query 'Id' should point to the same object type The CampaignMember has just ContactID and CampaignID. I'd assume because the CampaignMember is just some kind of shell for the contacs. One Contact can be CampaignMember of many Campaigns. – Lekü Jan 26 at 16:06
  • Does this change the double semi join approach or MUST there be an unique identifier like CampaignMemberId which I can't identify? I'd appreciate your feedback. – Lekü Jan 26 at 16:07
  • @Lekü My apologies, I forgot the shape of that data model. Revised my answer. – David Reed Jan 26 at 16:53
  • Thank you very much, David. It worked like a charm. The best part is that thanks to your help I totally understood the query/situation! – Lekü Jan 26 at 16:58

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.