Complex query possible?

Question

I have 3 tables, a parent table and 2 child tables. Lets say Mother_c is the parent. Then Child_c and Pet_c are the 2 child tables that have master-detail relationship pointer to Mother_c.

I have the Id of one row from Child_c, I want to retrieve all the rows from Pet_c that correspond to the Mother_c of that single Child_c row.

I'm wondering if this is possible in one SOQL query?

Answer 1

Yes, this is totally possible with semi-join SOQL. I tested this out with the standard CRM objects like this:

SELECT Id,
      (SELECT Id FROM Cases) 
FROM Account 
WHERE Id IN (SELECT AccountId 
             FROM Contact 
             WHERE Id = '0036000000qCwp9'
            )

To walk you through this, with a given Contact id, you first find the parent Account, and then traverse back down to the child Cases. In your example with custom objects, it would be very similar, but would use the __r custom relationships names instead:

SELECT Id,
      (SELECT Id FROM Pet__r) 
FROM Mother__c 
WHERE Id IN (SELECT Mother__c 
             FROM Child__c 
             WHERE Id = '003a000000qCwp9'
            )