SOQL query with subquery

Question

I'm having trouble getting the results I want from a Salesforce/Apex/SOQL query. I want: A list of Contact objects containing only contacts who are CampaignMembers of a set of campaigns; and they should have the data from that Campaign member easily accessible. (my eventual goal is a VF page with a list of all Contacts connected to any of these campaigns with a grid indicating their status for each campaign.) These work: ``` Campaign[] cams = [SELECT id, name FROM Campaign WHERE parentid = '70170000000LRIe']; System.debug(cams); // returns ~4 Campaign objects CampaignMember[] cmembers = [SELECT id, status, contactid, campaignid FROM CampaignMember WHERE campaignid in :cams]; System.debug(cmembers); // returns about 40 CampaignMember objects. ``` Here's my problem: ``` Contact[] members = [SELECT id, firstname, lastname, (SELECT id, status, comment__c, campaignid FROM Contact.CampaignMembers WHERE campaignid in :cams) FROM Contact]; System.debug(members); // contains ALL Contacts in th…

Accepted Answer

You could use a semi-join on contacts to filter the contacts to the set you want, something like this

```
[select id, firstname, lastname, 
     (select id, status, comment__c, campaignid from CampaignMembers)
     from contact where id in 
          (select contactId from campaignMember where campaignId in :cams];
```

Another option would be to drive from campaignMmeber instead.

```
[select contact.id, contact.firstname, contact.lastname, 
  status, comment__c, campaignId from campaignMembers 
  where contactId !='' and  campaignId in :cams];
```

SOQL query with subquery

Solution

Problem