-1

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!

Lekü
  • 53
  • 1
  • 6
  • Your query doesn't have a table `Contact` nor `Campaign`, so your query results in an error. Please show your *full* query – HoneyBadger Jan 26 '21 at 11:41

2 Answers2

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'
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • Hello! Thank you very much for your answer. It looks very promising but unfortunately I get the following error message: MALFORMED_QUERY: unexpected token: 'HAVING' Furthermore I don't get this bit: FROM CampaignMember c as c is the object contact. I'd be very happy if you have any idea how to troubleshoot this issue. Thank, in advance! – Lekü Jan 26 '21 at 12:24
  • 1
    @Lekü . . . There is no `FROM CampaignMember c as c` in this query. There is also no obvious syntax error, and the syntax works: https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=3b986d3fd96786fe3421570e1fc6563f. You can try it in different engines. – Gordon Linoff Jan 26 '21 at 12:57
  • @Gordan Linoff: I see, thank you for clarifying and the attached link. However, I can't run it in workbench.developerforce.com under SOQL Query. Any idea, how I can run it there without getting the error above? – Lekü Jan 26 '21 at 13:11
  • @Lekü . . . SOQL supports `GROUP BY` and `HAVING`. It is really unclear to me what the problem could be. – Gordon Linoff Jan 26 '21 at 13:34
  • @ Gordon Linoff . . . Sorry, SOQL Query in Workbench flagged the HAVING statement but the problem is: unexpected token: '*' I read it up and as I understand SOQL doesn't know the *. Anyways, thank you for your input which was really useful for a newbie like me. – Lekü Jan 26 '21 at 15:51
0

The other answer is related to general SQL not SOQL (also the Comment under your question is wrong as these are not tables but fields on Lookup fields - again SOQL specific thing).

Your query does exactly what you asked it to do - in the where clause:

Campaign.Name= 'A' AND Campaign.Name != 'B'

When Campaign.Name = 'A' it can never be equal to 'B' at the same time (because it is 'A').

There is separate CampaignMember record for every Contact & Campaign combination.

What you could try to do is something like:

SELECT Id FROM CampaignMember
WHERE Campaign.Name = 'A' 
  AND ContactId NOT IN (
   SELECT ContactId FROM CampaignMember
     WHERE Campaign.Name = 'B'
  )

However this will not work - as you can't have subquery on the same object as the main query in SOQL.

In the end the only solution is to query both Campaigns (either in 1 or 2 queries) & do the diff in Apex - you can do it in 1 for cycle - O(n).

Unfortunately SOQL itself can't do what you are trying to achieve.

Oto Šimeček
  • 1