function readOnly(count){ }
Sign Up ›
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
Learn More

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
John L Schneider IIJohn L Schneider II 

Accounts with Campaign Members

SELECT Contact.AccountId
FROM CampaignMembers
WHERE
Contact.AccountId NOT IN (SELECT Contact.AccountId FROM CampaignMember WHERE CampaignId = '7011A00000142mwQAA' AND Contact.Client_Options__c = 'Kick Me') AND
Contact.AccountId IN (Select Contact.AccountId FROM Contact WHERE Id IN (Select ContactId FROM CampaignMember WHERE CampaignId = '7011A00000142mwQAA')
ORDER BY Owner.Name ASC

I am trying to get a list of Accounts who have contacts that are members of a campaign (ID is in code) but don't have a contact that is a member of the same campaign with a contact.textfield (Client_Options__c) set to 'Kick Me'.

I'm running in to the "The inner and outer selects should not be on the same object type" error and can't figure out a way around this.  This is a query that is run on demand so either a revised query or something I could drop in to Execute Anonymouse would be fantastic!  I appreciate any guidance here!
Best Answer chosen by John L Schneider II
Emmanuel Cruz BEmmanuel Cruz B
Hi John,

Since Salesforce doesn't allow you to execute inner selects on the same object type you will need to get that set out of the query: 
set<Id> ExcludeSet = new set<Id>();
list<Account> AcctList = new list<account>();
for(CampaignMember cm :[    SELECT Contact.AccountId 
                            FROM CampaignMember 
                            WHERE Contact.Client_Options__c = 'Kick Me']){
    ExcludeSet.add(cm.Contact.AccountId);
} 
for(CampaignMember cm :[    SELECT Contact.AccountId, Contact.Account.Name 
                            FROM CampaignMember 
                            WHERE Contact.AccountId NOT IN :ExcludeSet 
                            AND CampaignId = '7011A00000142mwQAA'])
{
    AcctList.add(new Account(Id=cm.Contact.AccountId));
    System.debug('Account: '+cm.Contact.Account.Name);
}
In the other hand, If you need to keep only one query, you can get the list of account form with two simple for loops: 
set<Id> ExcludeSet = new set<Id>();
list<CampaignMember> cmList = [	SELECT Contact.AccountId, Contact.Account.Name, Contact.Client_Options__c
                               	FROM CampaignMember 
                        		WHERE Contact.AccountId NOT IN :ExcludeSet 
                        		AND CampaignId = '7011A00000142mwQAA'];
for(CampaignMember cm :cmList){
    if(cm.Contact.Client_Options__c == 'Kick Me'){
        ExcludeSet.add(cm.Contact.AccountId);
    }
}
list<Account> AcctList = new list<Account>();
for(CampaignMember cm :cmList){
    if(!ExcludeSet.contains(cm.Contact.AccountId)){
        AcctList.add(new Account(Id=cm.Contact.AccountId));
        System.debug('Account: '+cm.Contact.Account.Name);
    }
}
Hope this helps you.
Regards

All Answers

John L Schneider IIJohn L Schneider II
SELECT Contact.AccountId
FROM CampaignMember
WHERE
Contact.AccountId NOT IN (SELECT Contact.AccountId FROM CampaignMember WHERE CampaignId = '7011A00000142mwQAA' AND Contact.Client_Options__c = 'Kick Me') AND
Contact.AccountId IN (Select Contact.AccountId FROM CampaignMember WHERE CampaignId = '7011A00000142mwQAA')
ORDER BY Owner.Name ASC
The code snip I originally posted was incorrect, but this doesn't work either.
 
Emmanuel Cruz BEmmanuel Cruz B
Hi John,

Since Salesforce doesn't allow you to execute inner selects on the same object type you will need to get that set out of the query: 
set<Id> ExcludeSet = new set<Id>();
list<Account> AcctList = new list<account>();
for(CampaignMember cm :[    SELECT Contact.AccountId 
                            FROM CampaignMember 
                            WHERE Contact.Client_Options__c = 'Kick Me']){
    ExcludeSet.add(cm.Contact.AccountId);
} 
for(CampaignMember cm :[    SELECT Contact.AccountId, Contact.Account.Name 
                            FROM CampaignMember 
                            WHERE Contact.AccountId NOT IN :ExcludeSet 
                            AND CampaignId = '7011A00000142mwQAA'])
{
    AcctList.add(new Account(Id=cm.Contact.AccountId));
    System.debug('Account: '+cm.Contact.Account.Name);
}
In the other hand, If you need to keep only one query, you can get the list of account form with two simple for loops: 
set<Id> ExcludeSet = new set<Id>();
list<CampaignMember> cmList = [	SELECT Contact.AccountId, Contact.Account.Name, Contact.Client_Options__c
                               	FROM CampaignMember 
                        		WHERE Contact.AccountId NOT IN :ExcludeSet 
                        		AND CampaignId = '7011A00000142mwQAA'];
for(CampaignMember cm :cmList){
    if(cm.Contact.Client_Options__c == 'Kick Me'){
        ExcludeSet.add(cm.Contact.AccountId);
    }
}
list<Account> AcctList = new list<Account>();
for(CampaignMember cm :cmList){
    if(!ExcludeSet.contains(cm.Contact.AccountId)){
        AcctList.add(new Account(Id=cm.Contact.AccountId));
        System.debug('Account: '+cm.Contact.Account.Name);
    }
}
Hope this helps you.
Regards
This was selected as the best answer