0

Setup

  • I have three objects: X, Y, and Z.
  • X (child) has master-detail relationship to Y (parent).
  • Z (child) has a lookup relationship to Y (parent).

Question

How do I retrieve records from object X which are also related to object Z with Y? That is, results from X having common entry in Y.

  • Can you please clarify your question? Are you looking for X records whose Y has any child Z records? Or you already have some Z records and you are looking for X records that look up to their Y parents? Or something else entirely? – Adrian Larson Feb 25 '16 at 22:19
  • Question, does your "X" have a lookup to "Z"? because you mentioned "X which are also related to object Z with Y" – Sean Soriano Feb 25 '16 at 22:26
  • If Z is a (lookup) child of Y and Y is a (detail) child of X, then to traverse from X to Z requires two SOQL queries (X + Ys) and (Ys + Zs) – cropredy Feb 25 '16 at 23:18
  • Can you also clarify what object is master and which child in each relationship? – Sebastian Kessel Feb 25 '16 at 23:54
  • @crop1645 Pretty sure X is also a child of Y. – Adrian Larson Feb 25 '16 at 23:59
4

For simplicity I will rename your objects to:

  • X => Detail
  • Y => Parent
  • Z => Child

The most reasonable interpretation of your OP I can infer is:

If I have a collection of Child records, how can I find Detail records for the same Parent records?

If this interpretation is correct, you can do the following:

public static List<Detail__c> findSiblings(List<Child__c> children)
{
    Set<Id> parentIds = new Set<Id>();
    for (Child__c child : children) parentIds.add(child.Parent__c);
    return [SELECT Id FROM Detail__c WHERE Parent__c IN :parentIds];
}

Another interpretation I could see is:

How can I find all Detail records whose Parent has any Child records?

If this interpretation is correct, I would add a field on Parent along the lines of Has_Child_Records__c, which you can manage from Process Builder. Just set it up on Child to update the Parent checkbox with the criteria OR(ISNEW(), ISCHANGED(Parent__c)). Then you can just do:

public static List<Detail__c> findDetails()
{
    return [SELECT Id FROM Detail__c WHERE Parent__r.Has_Child_Records__c = true];
}

If neither of these interpretations is correct, please add more detail to your question to clarify.

2

You could query the parent and subquery the children, you can run this example in the Developer console, just change the Account Name filter, to see the results:

SELECT Name, (SELECT Name FROM Opportunities), (SELECT Name FROM Contacts) FROM Account WHERE Name = 'sForce'

To Adrians point if you wanted only parents that had both types of children you could do semi-joins like:

SELECT Id, Name, (SELECT Name FROM Opportunities), (SELECT Name FROM Contacts) FROM Account WHERE Name = 'sForce' 
AND Id IN (SELECT AccountId FROM Contact WHERE FirstName = 'Jake') AND Id IN (SELECT AccountId FROM Opportunity)

Or just one of the children types like:

SELECT Id, Name, (SELECT Name FROM Opportunities), (SELECT Name FROM Contacts) FROM Account WHERE Name = 'sForce' 
AND Id IN (SELECT AccountId FROM Contact WHERE FirstName = 'Jake')

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.