SOQL Query: Relationship Queries

Relationship queries allow us to fetch records from multiple objects but, these objects must share some kind of relationship. Relationship queries, particularly SOQL query allow developers and admins to retrieve and analyze related data efficiently.

Understanding Relationship Queries

These queries involve two types of relationship:

• Parent to Child Relationship – Querying on parent object and its related child object.
• Child to Parent Relationship – Querying on a child object and its related parent object.

Parent-to-Child Relationship Queries

These use Subqueries to retrieve related child records for a parent object. The main object for query is parent object. No more than 20 parent to child relationships can be specified in query.

Syntax

SELECT fieldApiName1, fieldApiName2, 
 (SELECT fieldApiName1, fieldApiName2 FROM childRelationshipName) 
FROM parentObjApiName

Examples of Parent-to-Child Relationship Query

Account and its related Contact child records

SELECT Id, Name, 
  (SELECT Id, Name FROM Contacts), 
Industry FROM Account

Output

Custom Object: Author and its related Books record

SELECT Id, Name, BirthDate__c, 
  (SELECT Id, Name, Genre__c FROM Books__r) 
FROM Author__c

Output

Multiple Child object records in single query: Account and its related Contact and Opportunity child records

SELECT Id, Name, Phone,
(SELECT Id, Name, ContactSource, Birthdate FROM Contacts),
(SELECT Id, Name, Amount, StageName FROM Opportunities)
FROM Account ORDER BY Name

Output

How to find Child relationship name while querying from Parent to Child?

Child relationship name is present at child object.

• Click on Gear Icon of top left corner.
• Click on Setup
• Click on Object Manager from menu bar
• Search for child object in Quick Find Box and Click on it.
• Click on Field & Relationships
• Search for relationship field which reference to parent Object and click on it
• Search for field Child Relationship Name field on UI.
• Make sure to append __r at the end of Custom Object while referencing it in SOQL query.

Below is the apex script to get Child Relationship Name using Parent object Api name and Child object Api name.

//Create sobject Instance 
sObject dynamicObj = (SObject)Type.forName('Author__c').newInstance(); //Enter apiName of Parent object
//get the type token
SObjectType sObjType = dynamicObj.getSObjectType();
//get describe
DescribeSobjectResult sObjDescribe = sObjType.getDescribe();
//loop through children
for(Schema.ChildRelationship childRels :sObjDescribe.getChildRelationships()){
   //find the child
   if(String.ValueOf(childRels.getChildSObject()) == 'Book__c'){ //Enter apiName of child Object
      System.debug(childRels.getRelationshipName());
   }
}

Child-to-Parent Relationship Queries

The main object is child object. Query is written on child object and fetch parent details using lookup or Master-Detail relationship field. Multiple parent records are not possible in child to parent query. Please note child to parent relationship query supports up to five levels of relationships.

Syntax

SELECT fieldApiNameOfParentObj.fieldApiNamesOfParentObj 
FROM childObjApiName

Examples of Child-to-Parent Relationship Query

Standard Object: Contact record and its parent account details

SELECT Id, Email, Account.Name, Account.Industry, Account.Language__c 
FROM Contact 
WHERE AccountId <> null AND Email <> null

Output

Custom Object: Book and its parent Author record

SELECT Id, Publication_Date__c, Author__r.Name, Author__r.Nationality__c 
FROM Book__c 
WHERE Author__r.Nationality__c = 'British'

Output

Multiple level deep parent record details

SELECT Id, Account.Owner.Name, Account.CreatedBy.Profile.Name 
FROM Contact

Output

In salesforce, relationship queries efficiently retrieve and manage related data from org. By following best practices such as optimizing query structure, respecting security and avoiding SOQL in loops, developers can create scalable code. Leveraging relationship queries not only enhances performance of application but also ensures seamless data-handling.