function readOnly(count){ }
Sign Up ›
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
Learn More

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
Justin MitchellJustin Mitchell 

Help with SOQL query - inner and outer selects same object

I'm using Conga Composer (so Apex isn't an option here) and trying to create a query. It looks like this: 
SELECT <fields>
FROM OpportunityLineItem
WHERE OpportunityId = <id>
AND OpportunityId IN (SELECT OpportunityId FROM OpportunityLineItem WHERE ProductCode = <product_code_1>)
AND ProductCode = <product_code_2>
When I try the above with real values I get an error: 
The inner and outer selects should not be on the same object type

Explanation:
I'm trying to recreate a report that shows fields from <product_2>, but only if the parent opportunity has products on it with <product_code_1>.
I can do this with a standard report, so I assume there must be a way to do it with SOQL. Here's a screenshot of what that looks like (Sales Price is an Opportunity Product field):
User-added image
 
Medhanie Habte 37Medhanie Habte 37
I think it's because you're using the query within the WHERE context whereas all queries should be written with in the SELECT context. This method is a more ideal starting point for querying two objects, in this Products and their respective Opportunities, you'll always want to refer child objects within the main query as a child query.
  
SELECT <fields> (SELECT OpportunityId FROM OpportunityLineItem WHERE ProductCode = <product_code_1>) FROM Opportunity WHERE ProductCode = <product_code_1> and so forth.

 
Justin MitchellJustin Mitchell
Hi,
Thanks for the quick response. That's not going to work though. The <fields> I have are all on the Opportunity Product (OpportunityLineItem) object, not on the Opportunity. Your query says "SELECT <fields> (...) FROM Opportunity..."
Alain CabonAlain Cabon
Hi,

Probably solved yet but here is the solution nevertheless.

"The inner and outer selects should not be on the same object type": it is a strong constraint of SOQL.

The solution is like that: 
SELECT id, (SELECT ProductCode ,OpportunityId FROM OpportunityLineItems)
FROM Opportunity
where id in (select OpportunityId from OpportunityLineItem WHERE ProductCode = 'GC5040')
  and id in (select OpportunityId from OpportunityLineItem WHERE ProductCode = 'GC1040')

The problem is to get all the values of (SELECT ProductCode ,OpportunityId FROM OpportunityLineItems)
We have to get these values with some apex code.

The result of this request is only the opportunities having the product codes 'GC5040' and 'GC1040' (at least).

The complete list of the product codes for these opportunities is given by:  (SELECT ProductCode ,OpportunityId FROM OpportunityLineItems)
Noticed the "s" at the end (it is the name of relationship for a standard object). That would be "__r" for a custom object.

Alain
Alain CabonAlain Cabon
In the Anonymous Window, we can get all the details like that: 
for (Opportunity opp : [SELECT id, (SELECT ProductCode FROM OpportunityLineItems) FROM Opportunity
where id in (select OpportunityId  from OpportunityLineItem WHERE ProductCode = 'GC5040')
  and id in (select OpportunityId  from OpportunityLineItem WHERE ProductCode = 'GC1040')]) {
      OpportunityLineItem[] opplist = opp.OpportunityLineItems;
      List<String> products = new List<String>();
      for (OpportunityLineItem pr:opplist) {
          system.debug('opportunity id:' + opp.id + ' product code: ' + pr.ProductCode );
          products.add(pr.ProductCode);
      } 
      system.debug('opportunity id:' + opp.id + '; all the products:' + String.join(products,','));
}