3

I have oppoortunities related to opportunities via a lookup, and a wanted to find all the opportunities with children opportunities. When I did it, I got the error - that inner and outer selects should not be the same object type.

Here is the offending query: 

Select id, name, stagename, (Select Id, name, stagename From Opportunities__r where stagename = 'Pre-Renewal' and type = 'Renewal') 
from opportunity 
where id in (select Parent_Opportunity__c from Opportunity)
Improve this question
4
  • 1
    @crmprogdev IMO, I find these to be 2 different questions. First one is asking how to fix his error. This question he encountered while trying to fix the original error and seems to be asking the why. The 2 answers should end up very different with the way the questions were worded.
    – dphil
    Commented Jan 5, 2015 at 21:11
  • IMO, he's still trying to solve the problem he has with querying Opps that only have child Opps as one single query, thus the reason I feel it's a duplicate. As its written, it's a circular or non-selective query.
    – crmprogdev
    Commented Jan 5, 2015 at 21:25
  • @crmprogdev can you elaborate on how it's non-selective?
    – PartOfTheOhana
    Commented Jan 7, 2015 at 15:00
  • Because of how it's written, it appears to me to be circular in nature, so therefore is non-selective. If you ran the outer query by itself, it doesn't appear as though you'd return a fixed set of results from which to capture the results on which to run the inner query. The answer to your original question & @JennyB answer below would seem to support that conclusion.
    – crmprogdev
    Commented Jan 7, 2015 at 15:21

1 Answer 1

Reset to default
6

Take a step back for a moment and try just this part of the query: 

Select id, name, stagename, (Select Id, name, stagename From Opportunities__r where stagename = 'Pre-Renewal' and type = 'Renewal') FROM Opportunity

I just tested out your scenario with a custom lookup field from Opportunity to Opportunity and the query did not error at all. I do see a problem with your WHERE clause because you cannot have the inner and outer selects on the same object type. It is mentioned in the docs here:

You cannot query on the same object in a subquery as in the main query. You can write such self semi-join queries without using semi-joins or anti-joins. For example, the following self semi-join query is invalid:

SELECT Id, Name
FROM Account
WHERE Id In (
   SELECT ParentId
   FROM Account
   WHERE Name = 'myaccount'
)

However, it is very simple to rewrite the query in a valid form, for example:

SELECT Id, Name
FROM Account
WHERE Parent.Name = 'myaccount'

So you would select all the parent opportunity ids from the children records instead like: 

SELECT Parent_Opportunity__c , Id, Name, StageName FROM Opportunity WHERE StageName = 'Pre-Renewal and Type = 'Renewal' instead.
Improve this answer
0

Not the answer you're looking for? Browse other questions tagged .