Create Relationship Queries with Standard Objects

Learning Objectives

After completing this unit, you'll be able to:

• Describe two types of relationship query.
• Identify the relationship between two objects.
• Create a child-to-parent query using dot notation.
• Create a parent-to-child query using a subquery.
• Use a subquery to filter query results.

Follow Along with Trail Together

Want to follow along with an expert as you work through this step? Take a look at this video, part of the Trail Together series on Trailhead Live.

(This clip starts at the 36:18 minute mark, in case you want to rewind and watch the beginning of the step again.)

About Relationship Queries

As a Salesforce admin you're already familiar with formula fields that pull data from related records. Now you can apply your knowledge of object relationships to writing SOQL queries.

Although the FROM clause is limited to one object, we can access two related objects by using a relationship query. A relationship query relies on the relationship between two different objects to return fields from both objects. Think of it as a cross-object query.

Consider this requirement:

“Get a list of all contacts with each contact's account name.”

We can query the Contact object, but it doesn't have an Account Name field. The account name is actually the Name field on the Account that's related to the Contact. In an org, you use a formula field to access a field on any related object. In Apex, you use a relationship query, but the type of relationship query depends on how the two objects are related.

To get records for a:

• Child object, and include fields from a related parent object, use a child-to-parent query.
• Parent object, and include fields from a related child object, use a parent-to-child query.

Before we can decide which type of query to use, we need to know how our two objects, Contact and Account, are related. Which is the parent and which is the child?

Identify the Objects' Relationship

As an admin, you're familiar with master-detail relationships in an org. A master-detail relationship is a parent-to-child relationship. The master object is the parent, and the detail object is the child. If the two objects you want to include in a query have a master-detail relationship, then you know which is the parent and which is the child.

A master-detail relationship is a one-to-many relationship. The master object (the parent) can have many detail objects (children), but each detail object (child) has only one master object (parent).

If you're not familiar with the two objects you want to include in a query, or you don't know how they're related, explore the objects in the Object Manager. Start with the object that is more likely to be the child and look at its Fields & Relationships. Find a field name that might connect to the parent object.

For example, Contact has an AccountId field with the data type Lookup(Account). Remember, in code we need the field name (AccountId), not the field label (Account Name).

In the details for the AccountId field, the Child Relationship Name is Contacts, plural. By default, the relationship name for standard objects is the plural form of the child object name.

The Child Relationship Name confirms that Contact is a child of Account. It also gives us the relationship name that we need for a parent-to-child query (which we address later in this unit).

Create a Child-to-Parent Query

Now we know that Contact and Account have a child-to-parent relationship. Contact is the child and Account is the parent. To return all contacts (children) and each contact's account (parent) name, we need a child-to-parent query.

In a child-to-parent query, you query the child object and get fields from a parent object by using dot notation, like this:

SELECT Name, Account.Name FROM Contact

This SELECT statement queries the Contact object. In the SELECT clause, Name is the Contact's Name field, and Account.Name is the Name field of the Account that's related to the Contact.

Run the Child-to-Parent Query

1. In the Developer Console's Query Editor, type:

   SELECT Name, Account.Name FROM Contact

2. Click Execute.

The first six rows of your results should be:

The second column shows the account name from the Contact's parent Account object.

Create a Parent-to-Child Query

Now let's consider how to query a parent object and get fields from its child object. In a parent-to-child query, we use a subquery to get fields from the child object. A subquery is a SELECT statement enclosed in parentheses and nested within another query.

Suppose that our requirement is:

“For every account, return the account name and the name of each related contact.”

When we read “of each related” we know we need a relationship query. Because Contact is a child of Account, we need a parent-to-child relationship query, which contains a subquery of the child object.

The first part of the requirement, “For every account, return the account name” becomes the main query, which forms the outer part of the full query. The second part of the requirement, “the name of each related contact” becomes the subquery, the inner query nested within the main query.

First we define the main query: SELECT Name FROM Account

Next, we define the subquery in parentheses: (SELECT Name FROM Contact)

Then, we position the subquery as if it were the second field in the main query. We add a comma after Name and then insert the subquery, like this:

Within a subquery, instead of the related object's field name, we use the child relationship name. So as the final step, we change the subquery to use the child relationship name Contacts, instead of the object name Contact, like this:

This query returns the account name and all contact names for all accounts. Let's break that down.

This query runs in three parts:

1. The query selects an account and gets the account Name field. That's the main query (the outer query).
2. Next, the query looks at the account's related contacts, and gets the Name field for each. That's the subquery (the inner query).
3. Then it moves on to the next account and repeats the process until it has selected all accounts.

Run the Parent-to-Child Query

1. In the Query Editor, type:

   SELECT Name, (SELECT Name FROM Contacts) FROM Account

2. Click Execute.

The first six rows of your results should look like this:

Interesting! The Query Results window displays account names and a collection of child contact records associated with each account. This type of collection probably looks familiar because we discussed maps in the Object-Oriented Programming for Admins module.

If you don't remember what a map is, don't worry, let's review. A map collection contains key-value pairs separated by commas. The key is the field name, such as Name. The key is followed by its value, such as Jake Llorrac. The key value pairs are returned by the nested SELECT statement that queries the contacts related to the account.

Filtering with a Subquery

Suppose we want only accounts that have a related contact with the last name Forbes. This is tricky because we want to filter the parent object, Account, by a value in the child object, Contact. In the same way that we nested a subquery within the main query, we can use a subquery (of the child object) in the main query's WHERE clause. The results of the subquery act like a filter for the main query. If you're familiar with cross filters, adding the WHERE clause in the subquery is equivalent to using a cross filter in a report.

We start with our existing query:
SELECT Name, (SELECT Name FROM Contacts) FROM Account

Next, we need a subquery that gets the AccountId field from contacts that have the last name Forbes:

(SELECT AccountId FROM Contact WHERE LastName = 'Forbes')

You may be wondering why we need the contact's AccountId field. We check the contact's AccountId field to find contacts that are related to an account. A contact is related to an account when the AccountId field on the Contact object and the Id field on the Account object have the same value (Contact.AccountId = Account.Id). So we query the Contact object and return its AccountId value. To select the account that's related to the contact, we find the account that has that value in its Id field.

Finally, we add a WHERE clause to the main query. We want to select an Account record only if its Id is found in the AccountId field of a Contact returned by the subquery. So we add WHERE Id IN followed by the subquery, like this:

SELECT Name, (SELECT Name FROM Contacts) FROM Account WHERE Id IN (SELECT AccountId FROM Contact WHERE LastName = 'Forbes')

This query does four things.

1. The query finds contacts with the last name Forbes and returns the value of each contact's AccountId field. (The WHERE clause subquery does that.)
2. Next, it finds the value of that AccountId in the Id field of an account and gets the name for that account. (The main WHERE clause does that.)
3. Then the query looks at the account's related contacts, and gets the name of each contact. (The main query's subquery does that.)
4. Finally, the main query returns the name of each account that has a related contact with the last name Forbes, and for each of those accounts, the names of all related contacts.

Run the Filtered Query

1. In the Query Editor, type:

   SELECT Name, (SELECT Name FROM Contacts)
   FROM Account
   WHERE Id IN (SELECT AccountId FROM Contact WHERE LastName = 'Forbes')

2. Click Execute.

Your results should look like this:

Summary

Now you know how to choose and build the right type of relationship query for standard objects. In the next unit, you learn about relationship queries for custom objects.

Resources

• Salesforce Developer Documentation: Understanding Relationship Names
• Salesforce Developer Documentation: Using Relationship Queries