Relationship Queries

Client applications need to be able to query for more than a single type of object at a time. SOQL provides syntax to support these types of queries, called relationship queries, against both standard objects and custom objects.

Relationship queries traverse parent-to-child and child-to-parent relationships between objects to filter and return results. They are similar to SQL joins. You cannot perform arbitrary SQL joins, however. The relationship queries in SOQL must traverse a valid relationship path as defined in the rest of this section.

You can use relationship queries to search for objects of one type based on criteria that applies to objects of another type, for example, "return all accounts created by Bob Jones and the contacts associated with those accounts." There must be a parent-to-child or child-to-parent relationship connecting the objects. You can’t write arbitrary queries such as "return all accounts and users created by Bob Jones."

Use the following topics to understand and use relationship queries in SOQL:

Understanding Relationship Names
Using Relationship Queries
Understanding Relationship Names and Custom Objects and Custom Fields
Understanding Query Results
Lookup Relationships and Outer Joins
Identifying Parent and Child Relationships
Understanding Polymorphic Keys and Relationships
Understanding Relationship Query Limitations
Using Relationship Queries with History Objects
Using Relationship Queries with the Partner WSDL

Understanding Relationship Names

Parent-to-child and child-to-parent relationships exist between many types of objects, for example, Account is a parent of Contact.

To be able to traverse these relationships for standard objects, a relationship name is given to each relationship. The form of the name is different depending on the direction of the relationship:

For child-to-parent relationships, the relationship name to the parent is the name of the foreign key, and there is a relationshipName property that holds the reference to the parent object. For example, the Contact child object has a child-to-parent relationship to the Account object, so the value of relationshipName in Contact is Account. These relationships are traversed by specifying the parent using dot notation in the query, for example:
```
 SELECT Contact.FirstName, Contact.Account.Name from Contact
```
This query returns the first names of all the contacts in the organization, and for each contact, the account name associated with (parent of) that contact.
For parent-to-child relationships, the parent object has a name for the child relationship that is unique to the parent, the plural of the child object name. For example, Account has child relationships to Assets, Cases, and Contacts among other objects, and has a relationshipName for each, Assets, Cases, and Contacts.These relationships can be traversed only in the SELECT clause, using a nested SOQL query. For example:
```
SELECT Account.Name, (SELECT Contact.FirstName, Contact.LastName FROM Account.Contacts) FROM Account
```
This query returns all accounts, and for each account, the first and last name of each contact associated with (the child of) that account.

Warning

You must use the correct naming convention and SELECT syntax for the direction of the relationship. For information about how to discover relationship names via your organization's WSDL or describeSObjects(), see Identifying Parent and Child Relationships. There are limitations on relationship queries depending on the direction of the relationship. See Understanding Relationship Query Limitations for more information.

Relationship names are somewhat different for custom objects, though the SELECT syntax is the same. See Understanding Relationship Names and Custom Objects and Custom Fields for more information.

Using Relationship Queries

You can query the following relationships using SOQL:

Query child-to-parent relationships, which are often many-to-one. Specify these relationships directly in the SELECT, FROM, or WHERE clauses using the dot (.) operator.
For example:
```
SELECT Id, Name, Account.Name FROM Contact WHERE Account.Industry = ’media’
```
This query returns the ID and name for only the contacts whose related account industry is media, and for each contact returned, the account name.
Query parent-to-child, which are almost always one-to-many. Specify these relationships using a subquery (enclosed in parentheses), where the initial member of the FROM clause in the subquery is related to the initial member of the outer query FROM clause. Note that in subqueries, you should specify the plural name of the object, as that is the name of the relationship for each object.
For example:
```
SELECT Name, (SELECT LastName FROM Contacts) FROM Account
```
The following query returns the name for all the accounts, and for each account, the last name of each contact.
Traverse the parent-to-child relationship as a foreign key in an aggregate query:
For example:
```
SELECT Name, (SELECT CreatedBy.Name FROM Notes) FROM Account
```
This query returns the accounts in an organization, and for each account, the name of the account, the notes for those accounts (which can be an empty result set if there were no notes on any accounts) with the name of the user who created each note (if the result set is not empty).

In a similar example, traverse the parent-to-child relationship in an aggregate query:

SELECT Amount, Id, Name, (SELECT Quantity, ListPrice,
  PricebookEntry.UnitPrice, PricebookEntry.Name 
  FROM OpportunityLineItems) FROM Opportunity

Using the same query, you can get the values on Product2 by specifying the product family (which points to the field's data):

SELECT Amount, Id, Name, (SELECT Quantity, ListPrice,
  PriceBookEntry.UnitPrice, PricebookEntry.Name,
  PricebookEntry.product2.Family FROM OpportunityLineItems)
  FROM Opportunity

WHERE clauses can be placed on any query (including subqueries), and apply to the root element of the FROM clause of the current query. These clauses can filter on any object in the current scope (reachable from the root element of the query), via the parent relationships.
For example:
```
SELECT Name, (SELECT lastname FROM Contacts WHERE CreatedBy.Alias = 'x') 
 FROM Account WHERE industry = ’media’
```
This query returns the name for all accounts whose industry is media, and for each account returned, returns the last name of every contact whose created by alias is 'x.'

Understanding Relationship Names and Custom Objects and Custom Fields

Custom objects can participate in relationship queries. Salesforce.com ensures that your custom object names, custom field names, and the relationship names associated with them remain unique, even if a standard object with the same name is available now or in the future. This is important in relationship queries, where the query traverses relationships using the object, field, and relationship names.

This section explains how relationship names for custom objects and custom fields are created and used.

When you create a new custom relationship in the Salesforce user interface, you are asked to specify the plural version of the object name, which you use for relationship queries:

Notice that the Child Relationship Name (parent to child) is the plural form of the child object name, in this case Daughters.

Once the relationship is created, notice that it has an API Name, which is the name of the custom field you created, appended by __c (underscore-underscore-c):

When you refer to this field via the API, you must use this special form of the name. This prevents ambiguity in the case where salesforce.com may create a standard object with the same name as your custom field. The same process applies to custom objects—when they are created, they have an API Name, the object named appended by __c, which must be used.

When you use a relationship name in a query, you must use the relationship names without the __c. Instead, append an __r (underscore underscore r).

For example:

When you use a child-to-parent relationship, you can use dot notation:
```
SELECT Id, FirstName__c, Mother_of_Child__r.FirstName__c FROM Daughter__c WHERE Mother_of_Child__r.LastName__c LIKE 'C%'
```
This query returns the ID and first name of daughter objects, and the first name of the daughter's mother if the mother's last name begins with 'C.'
Parent-to-child relationship queries do not use dot notation:
```
 SELECT LastName__c, (SELECT LastName__c FROM Daughters__r) FROM Mother__c
```
The example above returns the last name of all mothers, and for each mother returned, the last name of the mother's daughters.

Understanding Query Results

Query results are returned as nested objects. The primary or "driving" object of the main SELECT query contains query results of subqueries.

For example, you can construct a query using either parent-to-child or child-to-parent syntax:

Child-to-parent:
```
SELECT Id, FirstName, LastName, AccountId, Account.Name FROM Contact 
  WHERE Account.Name LIKE ’Acme%’
```
This query returns one query result (assuming there were not too many returned records), with a row for every contact that met the WHERE clause criteria.
Parent-to-child:
```
SELECT Id, Name, (SELECT Id, FirstName, LastName FROM Contacts) FROM Account
  WHERE Name like ’Acme%’
```
This query returns a set of accounts, and within each account, a query result set of Contact fields containing the contact information from the subquery.

Subquery results are like regular query results in that you may need to use queryMore() to retrieve all the records if there are many children. For example, if you issue a query on accounts that includes a subquery, your client application must handle results from the subquery as well:

Perform the query on Account.
Iterate over the account QueryResult with queryMore().
For each account object, retrieve the contacts QueryResult.
Iterate over the child contacts, using queryMore() on each contact's QueryResult.

The following sample illustrates how to process subquery results:

private void querySample() {
        QueryResult qr = null;
        try {
            qr = binding.query("SELECT a.Id, a.Name, (SELECT c.Id, c.firstname, 
                 c.lastname FROM a.Contacts c) FROM Account a");
            boolean done = false;
            if (qr.getSize() > 0) {
                while (!done) {
                    for (int i = 0; i < qr.getRecords().length; i++) {
                        Account acct = (Account)qr.getRecords(i);
                        String name = acct.getName();
                        System.out.println("Account " + (i + 1) + ": " + name);
                        printContacts(acct.getContacts());
                    }
                    if (qr.isDone()) {
                        done = true;
                    } else {
                        qr = binding.queryMore(qr.getQueryLocator());
                    }
                }
            } else {
                System.out.println("No records found.");
            }
            System.out.println("\nQuery succesfully executed.");
        }
        catch (RemoteException ex) {
            System.out.println("\nFailed to execute query succesfully, error message 
                 was: \n" + ex.getMessage());
        }
    }
    
    private void printContacts(QueryResult qr) throws RemoteException {
        boolean done = false;
        if (qr.getSize() > 0) {
            while (!done) {
                for (int i = 0; i < qr.getRecords().length; i++) {
                    Contact contact = (Contact)qr.getRecords(i);
                    String fName = contact.getFirstName();
                    String lName = contact.getLastName();
                    System.out.println("Child contact " + (i + 1) + ": " + lName 
                         + ", " + fName);
                }
                if (qr.isDone()) {
                    done = true;
                } else {
                    qr = binding.queryMore(qr.getQueryLocator());
                }
            }
        } else {
            System.out.println("No child contacts found.");
        }
    }

Lookup Relationships and Outer Joins

Beginning with Version 13.0 of the API, relationship queries return records even if the relevant foreign key field has a null value, as you would expect with an outer join. The change in behavior applies to the following types of relationship queries:

In an ORDER BY clause, if the foreign key value in a record is null, the record is returned in Version 13.0 and later, but not returned in versions before 13.0. For example:
```
SELECT Id, CaseNumber, Account.Id, Account.Name FROM Case ORDER BY Account.Name
```
Any case record for which AccountId is empty is returned in Version 13.0 and later.
The following example uses custom objects:
```
SELECT ID, Name, Parent__r.id, Parent__r.name FROM Child__c ORDER BY Parent__r.name
```
This query returns the Id and Name values of the Child object and the Id and name of the Parent object referenced in each Child, and orders the results by the parent name. In version 13.0 and later, records are returned even if Parent__r.id or Parent__r.name are null. In earlier versions, such records are not returned by the query.
In a WHERE clause using OR, if the foreign key value in a record is null, the record is returned in Version 13.0 and later, but not returned in versions before 13.0. For example, if your organization has one contact with the value of its LastName field equal to foo and the value of itsAccountId field equal to null, and another contact with a different last name and a parent account named bar, the following query returns only the contact with the last name equal to bar:
```
SELECT Id FROM Contact WHERE LastName = 'foo' or Account.Name = 'bar'
```
The contact with no parent account has a last name that meets the criteria, so it is returned in version 13.0 and later.
In a WHERE clause that checks for a value in a parent field, if the parent does not exist, the record is returned in Version 13.0 and later, but not returned in versions before 13.0.. For example:
```
SELECT Id FROM Case WHERE Contact.Lastname = null
```
Case record Id values are returned in version 13.0 and later, but are not returned in versions before 13.0.

Identifying Parent and Child Relationships

You can identify parent-child relationships by viewing the ERD diagrams in Data Model. However, not all parent-child relationships are exposed in SOQL, so to be sure you can query on a parent-child relationship by issuing the appropriate describe call. The results contain parent-child relationship information.

You can also examine the Enterprise WSDL for your organization:

To find the names of child relationships, look for entries that contain the plural form of a child object and end with type="tns:QueryResult". For example, from Account:

             <complexType name="Account">
                <complexContent>
                  <extension base="ens:sObject">
                    <sequence>
                      ...
                      <element name="Contacts" nillable="true" minOccurs="0"
                                     type="tns:QueryResult"/>
                      ...
                    </sequence>
                  </extension>
                </complexContent>
              </complexType>

In the example above, the child relationship name Contacts is in the entry for its parent Account.

For the parent of an object, look for a pair of entries such as AccountId and Account, where the ID field represents the parent object referenced by the ID, and the other represents the contents of the record. The parent entry has a non-primitive type, type="ens:Account".

              <complexType name="Opportunity">
                <complexContent>
                  <extension base="ens:sObject">
                    <sequence>
                      ...
                      <element name="Account" nillable="true" minOccurs="0"   
                              type="ens:Account"/>
                      <element name="AccountId" nillable="true" minOccurs="0" 
                              type="tns:ID"/>
                      ...
                    </sequence>
                  </extension>
                 </complexContent>
               </complexType>

For custom objects, look for a pair of entries with the relationship suffix __r:

          <complextType name="Mother__c">
           <complextcontent>
            <extension base="ens:sObject">
             <sequence>
               ...
               <element name="Daughters__r" nillable="true" minOccurs="0" 
                     type="tns:QueryResult"/>
               <element name="FirstName__c" nillable="true" minOccurs="0" 
                     type="xsd:string"/>
               <element name="LastName__c" nillable="true" minOccurs="0" 
                     type="xsd:string"/>
               ...
              </sequence>
             </extension>
            </complexContent>
           </complextType>

          <complextType name="Daughter__c">
           <complextcontent>
            <extension base="ens:sObject">
             <sequence>
               ...
               <element name="Mother_of_Child__c" nillable="true" minOccurs="0" 
                     type="tns:ID"/>
               <element name="Mother_of_Child__r" nillable="true" minOccurs="0" 
                     type="xsd:string"/>
               <element name="LastName__c" nillable="true" minOccurs="0" 
                     type="ens:Mother__c"/>
               ...
              </sequence>
             </extension>
            </complexContent>
           </complextType>

Understanding Polymorphic Keys and Relationships

A polymorphic key is an ID that can refer to more than one type of object as a parent. For example, either a contact or a lead can be the parent of a task. In other words, the WhoId field of a task can contain the ID of either a contact or a lead. If an object can have more than one type of object as a parent, the polymorphic key points to a Name object instead of a single object type.

Executing a describeSObjects() call returns the Name object, whose field Type contains a list of the possible object types that can parent the queried object. The namePointing field in the DescribeSObjectResult indicates that the relationship points to the Name object, needed because the relationship is polymorphic. For example, the value in WhoId on Task can be a contact or lead.

In order to traverse relationships where the object type of the parent is not known, you can use these fields to construct a query:

owner: This field represents the object of a parent who owns the child object, regardless of the parent's object type. For example:
```
      SELECT Id, Owner.Name FROM Task WHERE Owner.FirstName like ’B%’
```
This example query works for tasks whose owners are either calendars or users.
who: This field represents the object type of the parent associated with the child:
```
     SELECT Id, Who.FirstName, Who.LastName FROM Task WHERE Owner.FirstName LIKE ’B%’ 
```
This example query works for tasks whose owners can be either calendars or users, and whose "who" parent can be either contacts or leads.
what: This field represents the object type of a parent that is associated with the child where the object represents something other than a person (that is, not a contact, lead, or user):
```
      SELECT Id, What.Name FROM Event
```
This example query works for events whose parent may be an account or a solution, or any of another number of object types.

You can also use describeSObjects() to obtain information about the parents and children of objects. For more information, see describeSObjects() and especially namePointing, which, if set to true, indicates the field points to a name.

Understanding Relationship Query Limitations

When designing relationship queries, consider these limitations:

Relationship queries are not the same as SQL joins. You must have a relationship between objects to create a join in SOQL.
No more than 20 relationships can be specified in a query.
In each specified relationship, no more than five levels can be specified in a child-to-parent relationship. For example, Contact.Account.Owner.FirstName (three levels).
In each specified relationship, only one level of parent-to-child relationship can be specified in a query. For example, if the FROM clause specified Account, the SELECT clause could only specify the Contact or other objects at that level. It could not specify a child object of Contact.
You can query notes and attachments to get information about them, but you cannot filter on the body of the note or attachment. You cannot filter against the content of textarea fields, blobs, or Scontrols in any object. For example, the following query is valid, and returns all account names and the owner ID for any notes associated with the account:
```
SELECT Account.Name, (SELECT Note.OwnerId FROM Account.Notes) FROM Account
```
However, the following query is not valid, because it attempts to evaluate information stored in the body of the note:
```
SELECT Account.Name, (SELECT Note.Body FROM Account.Notes WHERE Note.Body LIKE 'D%') FROM Account
```
If you remove the WHERE clause, the query is valid and returns the contents of the body of the note:
```
SELECT Account.Name, (SELECT Note.Body FROM Account.Notes) FROM Account
```

Using Relationship Queries with History Objects

Custom objects and some standard objects have an associated history object that tracks changes to an object record. You can use relationship queries to traverse a history object to its parent object. For example, the following query returns every history row for Foo__c and displays the name and custom fields of Foo:

     SELECT OldValue, NewValue, Parent.Id, Parent.name, Parent.customfield__c 
        FROM foo__history

This example query returns every Foo object row together with the corresponding history rows in nested subqueries:

     SELECT Name, customfield__c, (SELECT OldValue, NewValue FROM foo__history) 
       FROM foo__c

Using Relationship Queries with the Partner WSDL

The partner WSDL does not contain the detailed type information available in the enterprise WSDL to get the information you need for a relationship query. You must first execute a describeSObjects() call, and from the results, gather the information you need to create your relationship query:

The relationshipName value for one-to-many relationships, for example, in an Account object, the relationship name for the asset child is Assets.
The reference fields available for the relevant object, for example, whoId, whatId, or ownerId on a Lead, Case, or custom object.

For an example of using the partner WSDL with relationship queries, see examples on developer.force.com (requires login).