Query to get records from 3 object relationship

Question

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.

Answer 1

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.

Answer 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')