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
merthenmerthen 

SQOL Query Join Problem with filter in sub-select

I have an issue where the below query is returning ALL Opportunities where the CloseDate>2010-01-01 but I only want the Opportunities where the subselect is valid.

 

Select o.Id, o.CloseDate, (Select TotalPrice From OpportunityLineItems where TotalPrice>1000000) From Opportunity o where o.CloseDate>2011-01-01

 

Is there any way to do this in SOQL while keeping the returned rows Opportunity rows and retuning ONLY the Opportunities having lines where the TotalPrice is greater than 1000000?

 

Obviously, this can be done in this manner returning OpportunityLineItem rows...but this is not what is desired

Select o.TotalPrice, Opportunity.closeDate, Opportunity.id From OpportunityLineItem o where o.TotalPrice>1000000 and Opportunity.closeDate>2011-01-01

 

Best Answer chosen by Admin (Salesforce Developers) 
SuperfellSuperfell

You can do both your original soql-r query, and the semi-join.

 

select id, (select blah blah blah from opportunityLineItems) from opportunity where id in (select opportunityId from .....)

 

All Answers

SuperfellSuperfell

Use a semi-join, something like

 

select id, ... from opportunity where id in (select opportunityId from opportunityLineItem where totalPrice > 1000000)
merthenmerthen

I modified the query as follows...

 

Select Id, CloseDate from Opportunity where id in (Select opportunityId From OpportunityLineItem where TotalPrice>1000000)  and CloseDate>2011-01-01

and, yes, it works correctly.   BUT...

 

I need some columns returned from the OpportunityLineItem as well.

So, if I modify the above like this...

 

Select Id, CloseDate from Opportunity where id in (Select opportunityId,quantity From OpportunityLineItem where TotalPrice>1000000)  and CloseDate>2011-01-01

 

Of course, it doesn't work anymore because of the 'quantity' in the subselect.

Is there a way to get more columns from OpptyLineItem returned?

 

 

 
SuperfellSuperfell

You can do both your original soql-r query, and the semi-join.

 

select id, (select blah blah blah from opportunityLineItems) from opportunity where id in (select opportunityId from .....)

 

This was selected as the best answer
merthenmerthen

thanks, Simon...its working

I was under the impression that I couldn't do more than one sub select in a query