SOQL/SQL: Result of left outer join query is not correct

Question

I am quite stuck regarding a query. I have three objects in Salesforce: Contact, CampaignMember, Campaign. Now, I want to get all the contacts which are in campaign 'A' but NOT in campaign 'B'. My code belwo doesn't work:

SELECT Contact.FirstName, Contact.LastName, Contact.ID FROM CampaignMember WHERE Campaign.Name= 'A' AND Campaign.Name != 'B'

Salesforce simply ignores the part:

 AND Campaign.Name != 'B'

Nevertheless I get all the contacts which are in 'A'. Not the difference between A&B, which I wanted.

If I switch to != Bonly, it returns zero contacts, which is not correct.

I suppose it is because the link between campaign and contact is made through the campaignmember object and I am trying to do a left outer join on the campaign where I want the contacts of campaign 'A' which are not in 'B'.

Is there a way to get this done? I would appreciate any help.

Thank you very much!

question from:https://stackoverflow.com/questions/65900649/soql-sql-result-of-left-outer-join-query-is-not-correct

Answer 1

Use aggregation. Assuming no duplicates:

SELECT c.FirstName, c.LastName, c.ID
FROM CampaignMember c
WHERE c.Name IN ('A', 'B')
GROUP BY c.FirstName, c.LastName, c.ID
HAVING COUNT(*) = 1 AND MAX(c.Name) = 'A';

If you do have duplicates, you can use:

HAVING MIN(c.Name) = MAX(c.Name) AND MAX(c.Name) = 'A'