I am trying to query Opportunities which have a Product matching certain criteria on, something like below:
SELECT Name FROM Opportunity WHERE type != 'renewal' AND Id IN (SELECT OpportunityId FROM OpportunityLineItem WHERE pricebookentry.product2.custom__c = 'value')
While the above doesn't cause an error no rows are returned. Is this possible or is my soql not formed correctly?
Edit: Splitting the SOQLS, the Opportunity part returns rows as expected. The OpportunityLineItem part doesn't:
SELECT OpportunityId FROM OpportunityLineItem WHERE pricebookentry.product2.custom__c = 'value'
So I believe I am doing something wrong with the OLI to Product part: