+ Start a Discussion
Casey Hardesty 10Casey Hardesty 10 

Soql query to pull Leads not attached to any campaigns

Hey everybody,

I'm running into a challenge that is probably very easy but I'm struggling with it. I need to write a SOQL query that will simple pull all Leads not attached to a Campaign. Here is what I've come up with so far but it is definitely not working...

SELECT Id, (Select Id From CampaignMembers WHERE Id = Null) FROM Lead WHERE CreatedDate >= 2016-03-13T00:00:00Z

When I go through the results, most of these are indeed attached to a campaign so not sure what I'm doing wrong.

Any ideas? Thanks in advance.
Best Answer chosen by Casey Hardesty 10
Veena Sundara-HeraguVeena Sundara-Heragu
Since you want leads that are not in campaigns, you need to look at "LeadId" field of CampaignMember and not tge "Id"

So, you could do the following:

Select Id from Lead where Id not in (Select LeadId from CampaignMember) AND CreatedDate >= 2016-03-13T00:00:00Z
 

All Answers

Veena Sundara-HeraguVeena Sundara-Heragu
Since you want leads that are not in campaigns, you need to look at "LeadId" field of CampaignMember and not tge "Id"

So, you could do the following:

Select Id from Lead where Id not in (Select LeadId from CampaignMember) AND CreatedDate >= 2016-03-13T00:00:00Z
 
This was selected as the best answer
Casey Hardesty 10Casey Hardesty 10
Thank you Veena. That worked perfectly. I appreciate the help.
David Roberts 4David Roberts 4
Yes, thanks. Worked for me.
You might want to qualify that the lead has not been converted to a contact:
Select Id,name,owner.name from Lead where Id not in (Select LeadId from CampaignMember) and isconverted = false

having said that, I'm not sure why that's different to using [ status != 'Qualified' ]

...learning all the time.