4

I am relative new to Salesforce, Apex an SOQL. Since now I could help myself with Google, but unfortunately on this question no answer showed up.

We have a customer object which is called 'Fachgebiet' (german expression). This object is connected to Contact by the ContactID and the Master-Detail-Reference 'Fachgebiete'.

I would like to build a query where I can use the selected field of the subquery as a filter for the WHERE-Clause of the main query. Is this even possible?

Here is the query so far:

SELECT Id, Name, MailingCountry, Phone, Email, 
  ( SELECT Fachgebiet__c FROM Fachgebiete__r ),
 FROM Contact

I would like to have a query like this:

SELECT Id, Name, MailingCountry, Phone, Email, 
  ( SELECT Fachgebiet__c FROM Fachgebiete__r ),
 FROM Contact WHERE Fachgebiet__c = 'Biologie'

I've tried:

WHERE Fachgebiet__c = 'Biologie'
WHERE Fachgebiet__r.Fachgebiet__c = 'Biologie'

The first version gives no result, the second one gives an error that the relationship is unknown (ERROR: Didn't understand the relationship 'Fachgebiete__r' in field path).

Is there a way to accomplish this?

I know about 'upward' and 'downward' queries. But the example above is just a part of the whole query. In total I need to gain information from three subqueries / objects. So i cannot use the 'upward'-query-type, i think. Maybe I'am wrong and you can give me other advice. ;-)

Thanks Peter

2 Answers 2

8

If I understand your requirements you should be able to move your subquery to the WHERE clause(Semi-Join). Example:

ID field Semi-Join

You can include a semi-join in a WHERE clause. For example, the following query returns account IDs if an associated opportunity is lost:

This example is a parent-to-child semi-join from Account to Opportunity. Notice that the left operand, Id, of the IN clause is an ID field. The subquery returns a single field of the same type as the field to which it is compared. A full list of restrictions that prevent unnecessary processing is provided at the end of this section.

SELECT Id, Name
FROM Account
WHERE Id IN
  ( SELECT AccountId
    FROM Opportunity
    WHERE StageName = 'Closed Lost'
)

Here is another example from the docs here:

SELECT Id, (SELECT Id from OpportunityLineItems)
FROM Opportunity
WHERE Id IN
  (
    SELECT OpportunityId
    FROM OpportunityLineItem
    WHERE totalPrice > 10000
)

The query above results in the Opportunity, and it's children records only when the children meet the requirement.

2
  • Hey Jenny, thanks you very much. This is working great. :-) I could not vote you up, since i got to low reputation. :-( I have an additional question because i need to set three filters. I build my query with all three filters and got an error: 'Maximum 2 semi join sub-selects are allowed'. Is there a way I can avoid this? If there is no way in SOQL I will do the last filter in APEX with a loop. Thanks anyway. :-) Commented Jun 17, 2015 at 11:44
  • Thanks @Peter.Kippes and no problem on the upvote, glad I could help, but unfortunately it states in the docs due to the overhead you are limited to 2.
    – Jenny B
    Commented Jun 17, 2015 at 11:50
1

I hope this helps.

SELECT Id, Name, MailingCountry, Phone, Email, 
  ( SELECT Fachgebiet__c FROM Fachgebiete__r ),
 FROM Contact WHERE Fachgebiet__c = 'Biologie'

You can try using something like this:

 1.  SELECT Id, Fachgebiet__c, contact.Id from Fachgebiet__c WHERE Fachgebiet__c = 'Biologie'

And for your question about using inner query field in outer where clause, I guess you can't use that. Let me know if this helps.

1
  • Thanks for the answer, but Jenny B's solution did the trick. :-) I could not use your suggestion, because I need Information from three subqueries. Anyway, as I learned now, I can only use 2 semi-joins. I will set the third filter via APEX Loop. Commented Jun 17, 2015 at 12:21

You must log in to answer this question.

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