Get all contacts and leads from campaign members

Question

I can't think how to write a query to return all the contacts and all the leads (IDs, Names, and Phones) from CampaignMember.

I've never used much else rather than the standard [SELECT field FROM object WHERE criteria] format so I don't really know where to start.

At the start of the function I have the Id of the campaign, and two empty lists of Contact and Lead, which I'd like to populate. Can anyone help?

Answer 1

You should be able to do something like:

List<Contact> contacts = [
    SELECT 
        Id, Name, Phone 
    FROM 
        Contact 
    WHERE 
        Id IN (
            SELECT 
                ContactId 
            FROM 
                CampaignMember 
            WHERE 
                CampaignId = :campaignId AND ContactId != null
        )
];

List<Lead> leads = [
     SELECT 
         Id, Name, Phone 
     FROM 
         Lead 
     WHERE 
         Id IN (
             SELECT 
                 LeadId 
             FROM 
                 CampaignMember 
             WHERE 
                 CampaignId = :campaignId 
                 AND LeadId != null
         )
 ];

Answer 2

The following SOQL will give you the ContactId and LeadId for Campaigns with an particular id:

SELECT ContactId,LeadId FROM CampaignMember WHERE CampaignId='XXX'

If you need to get details of the contacts and leads you can just use relationships like the following:

SELECT ContactId, Contact.FirstName, Contact.LastName, Contact.Phone, LeadId, Lead.FirstName, Lead.LastName, Lead.Phone FROM CampaignMember WHERE CampaignId='XXX'

To get the lead and contacts separately you could do the following:

SELECT ContactId, Contact.FirstName, Contact.LastName, Contact.Phone FROM CampaignMember WHERE CampaignId='XXX' AND ContactId<>''

and

SELECT LeadId, Lead.FirstName, Lead.LastName, Lead.Phone FROM CampaignMember WHERE CampaignId='XXX' AND LeadId<>''