Nesting of semi join sub-selects is not supported

Question

Given Campaign Ids, List All Contacts Under Campaign that has some Opportunities associated. And I am using the below SOQL query for it. But it gets 'Nesting of semi-join sub-selects is not supported'. How do I get around that error? I know SOQL allows upward nesting and not downward. Here is my query below. Thanks !

SELECT contactid,contact.name,contact.account.name, role,opportunityid, opportunity.name
 FROM opportunitycontactrole
 WHERE contactid in (SELECT contactid FROM campaignmember WHERE campaignid in (SELECT id from campaign))

Answer 1

I don't understand the last part of your SOQL in which you are pointing the CampaingId to the Campaign object. Please, try this query instead:

List<OpportunityContactRole> ocr =[SELECT contactid,contact.name,contact.account.name,role,opportunityid opportunity.name
FROM opportunitycontactrole WHERE contactid in (SELECT contactid FROM campaignmember WHERE campaignid != null)];

It looks like you want to make sure that there is a campaign related to the OpportunityContactRole. By adding the !=null (not equal) we can accomplish that logic and avoid the error which it is a limitation in your SOQL.

UPDATE:

You can use a list of Campings and use it as your criteria as part of your SOQL.

List<Campaign> camList = [SELECT Id FROM Campaign]; 

List<OpportunityContactRole> ocr =[SELECT contactid, contact.name,  contact.account.name, role, opportunityid, opportunity.name
FROM opportunitycontactrole WHERE contactid in (SELECT contactid FROM campaignmember WHERE campaignid IN: camList)];

If you can pass the campaigns to thatcamListlist you could do something like the above query. You need to be aware of governor limits for the campaign list.

Answer 2

If you can define the criteria for the account filter in your external application then you could do something like this:

Select Id 
   From OpportunityContactRole 
   Where ContactId In 
      (Select Id From Contact Where Account.Name = 'test')