0

I need some help creating a SOQL query or confirmation that it cannot be done. The requirements are as follows:

  1. Custom Object 1 has a relation to Cases.
  2. Custom Object 2 has a relation to Cases.
  3. Selection criteria for the query are available only in Custom Object 1.
  4. I need all of Custom Object 2 related to the Cases that are related to the selected list of Custom Object 1.
  5. It needs to be done in a single SOQL query.

This will be called using Informatica and I do not have permission to make changes to the schemas or processes in Salesforce to make this simpler. I also do not have access to a database to sync with Custom Object 2 and use it as a lookup to do it cleanly in two queries. The current approach is to get all of Custom Object 2 and use it as a lookup, which is not scalable.

1 Answer 1

3

Yes, it can be done, provided your data volume is conducive to the query not timing out. What you want is something along the lines of

SELECT SOME_FIELDS 
FROM Custom_Object_2__c 
WHERE Case_Id__c IN (SELECT Case_Id__c 
                     FROM Custom_Object_1__c
                     WHERE CRITERIA_ARE_TRUE)

This construct is called a semi-join sub-select, and it's worth noting that you can't nest them (you'll get an exciting error message).

5
  • D'oh. Can't believed I missed that. Probably worth linking to and briefly explaining Left Inner Join.
    – Adrian Larson
    Commented Feb 21, 2018 at 18:42
  • I believe the sesquipedalian name of this construct is 'semi-join sub-select', and naturally I cannot find my favorite reference link... still looking.
    – David Reed
    Commented Feb 21, 2018 at 18:43
  • That's another name for it.
    – Adrian Larson
    Commented Feb 21, 2018 at 18:45
  • > sesquipedalian. That's a heck of an autological word.
    – JaredT
    Commented Feb 21, 2018 at 18:45
  • I do not often get to use my Classics degree in the tech world, but when I do...
    – David Reed
    Commented Feb 21, 2018 at 18:48

You must log in to answer this question.

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