soql

How to get all related information with a single SOQL query?

I have three objects: Master, Join, and Item. Join has a Master-Detail relationship to Master called Master__c and a lookup relationship to Item called Item__c.

Join
  ├───.Master__c     // <-- master-detail
  |      └───Master
  |       
  ├───.Item__c
  |      |
         └───Item  // <-- Lookup

Given a list of Master Ids, I want all of the Master records along with the names of the related Item records through Join. How do I do this?

Solution

  • If I understand the objects and relationships you described correctly,

    Master is the parent object and can have zero or more Join records. Join is a child of Master and can have zero or 1 reference to an Item.

    Master
  └──Join
       └──Item

    If so, you can use a SOQL query like this:

    SELECT id, name, 
                (SELECT name, item__r.name FROM Master__r) 
FROM Master 
WHERE id in (<list of master ids>)