Fetching records where the field value is null or similar to SOQL inner query

Question

There is a sObject called Promo_Association__c which have a lookup field(Partnership__c) on Account object. There is a quick action on Opportunity layout and the requirement is to fetch Promo Association records where the Account Id(Partnership__c) equals to null or similar to Account id in the relevant opportunity. The query is as follows.

ID partnerId = Id.valueOf(partnershipId);
String query  = SELECT Id,Partner__c FROM Promo_Association__c WHERE  (Partnership__c =null OR Partnership__c IN (SELECT AccountId FROM Opportunity WHERE id =:partnershipId));

This throws the following error.

Semi join sub-selects are only allowed at the top level WHERE expressions and not in nested WHERE expressions.

It would be helpful, if anyone can suggest a better way.

Answer 1

This won't work. The error is telling you that you can't have the outer parentheses in your query:

SELECT Id,Partner__c 
FROM Promo_Association__c 
WHERE Partnership__c =null OR Partnership__c IN (
  SELECT AccountId FROM Opportunity WHERE id =:partnershipId
)

However, that will only lead to your next error, namely "Semi join sub-selects are not allowed with the 'OR' operator."

Given these two restrictions, you will necessarily need to do two queries to get your full data set.