Salesforce Developer Soql Related Records


// Salesforce - Developer - SOQL - Querying Related Records:

Records in Salesforce can be linked to each other through relationships: lookup 
relationships or master-detail relationships. For example, the Contact has a 
lookup relationship to Account. When you create or update a contact, you can 
associate it with an account. The contacts that are associated with the same 
account appear in a related list on the account’s page. In the same way you can 
view related records in the Salesforce user interface, you can query related 
records in SOQL.

To get child records related to a parent record, add an inner query for the 
name, rather than a Salesforce object name. This example contains an inner query 
to get all contacts that are associated with each returned account. The FROM 
clause specifies the Contacts relationship, which is a default relationship on 
Account that links accounts and contacts.

SELECT Name, (SELECT LastName FROM Contacts) 
  FROM Account 
  WHERE Name = 'SFDC Computing'

Account[] acctsWithContacts = [
  SELECT Name, (SELECT FirstName,LastName FROM Contacts)
    FROM Account 
    WHERE Name = 'SFDC Computing'];

// Get child records
Contact[] cts = acctsWithContacts[0].Contacts;
System.debug('Name of first associated contact: ' 
             + cts[0].FirstName + ', ' + cts[0].LastName);

The FROM clause of the inner query runs against the relationship name, rather 
than a Salesforce object name.         

You can traverse a relationship from a child object (contact) to a field on its 
parent (Account.Name) by using dot notation. For example, the following Apex 
snippet queries contact records whose first name is Carol and is able to 
retrieve the name of Carol’s associated account by traversing the relationship 
between accounts and contacts.

Contact[] cts = [SELECT Account.Name FROM Contact 
                 WHERE FirstName = 'Carol' AND LastName='Ruiz'];
Contact carol = cts[0];
String acctName = carol.Account.Name;
System.debug('Carol\'s account name is ' + acctName);

The examples in this section are based on standard objects. Custom objects can 
also be linked together by using custom relationships. Custom relationship names 
end with the __r suffix. For example, invoice statements are linked to line 
items through the Line_Items__r relationship on the Invoice_Statement__c custom 
object. (These custom objects are part of the Warehouse schema used in the Workbook.)
We can use subquery in the field list if the subquery query a relationship:

SELECT Account.NAME, (SELECT Contact.LastName FROM Account.Contacts)
FROM Account;

The field list can also be an aggregate function such as COUNT() or 
Salesforce - Developer - SOQL  - Polymorphic relationship fields:

We can search polymorphic relationship fields on a SOQL query.  A polymorphic 
relationship is one where the current object can be one of several types 
depending on a related Event.  To filter on a polymorphic relationship field, 
use the Type qualifier:

WHERE polymorphicRelationship.Type comparisonExpression

polymorphicRelationship: a polymorphic relationship field in object being 
  queried that can reference multiple object types.  For example, the What 
  relationship field of an Event could be an Account, a Campaign, or an 

SELECT Id FROM Event WHERE What.Type IN ('Account', 'Opportunity');
Unless otherwise stated, the content of this page is licensed under Creative Commons Attribution-ShareAlike 3.0 License