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 the DB, but I wanted filtered results.
System.debug(members[x].CampaignMembers);
// this is a contact I've verified has a qualifying CampaignMember, but the list is empty.
// UPDATE: CampaignMembers are now being returned, not sure what changed...

~~Why aren't any CampaignMember objects being returned from the subquery?~~
Why isn't the Contact list being filtered? (well, obviously b/c there's no WHERE clause in it, but what WHERE clause provides what I want?)

I know I could do this by doing the CampaignMember query on its own and looping through it to prep a Contact query, but that seems like a lot of extra processing when a subquery should work.

Thanks!

Update

The CampaignMember objects are now showing up - oddly - I must have fixed some small typo without noticing (and yes, they're returning multiple columns and that seems to be fine).

I still can't figure out how to filter the Contact query, though...

SQL subqueries in the SELECT aren't allowed to return more than one column — OMG Ponies
Why you use a SQL subquery there? You can make a join query between the 2 tables!! As Omg Ponies said, the subquery on the select just can return 1 column per subquery!! — Nervo Verdezoto
This is salesforce SOQL, not SQL. There is no JOIN keyword in SOQL - you can only traverse relationships using subqueries (which do return more than one column). — Benj

superfell superfell · Accepted Answer · 2010-10-26T21:40:34

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

Here's my problem:

1 Answers