Related Records: Parent and Child Fields
In Part 1 of SOQL 101 for Salesforce Admins, I explained how to write a standard SOQL query. The keywords we covered were SELECT, FROM, WHERE, ORDER BY, and LIMIT. If you’re new to writing SOQL queries and missed Part 1, please review it before continuing.
In Part 2, I’m going to show you how to pull in fields from related objects. As you can imagine, this can be super helpful when you want to (1) expand the scope of your query (see more data!) and (2) filter on fields beyond the object you’re starting with. I’m also going to cover LIKE and IN, two comparative operators that will supercharge your WHERE clauses.
Direct Relationships: Master-Detail & Lookup
First things first. In order to pull in fields from another object, a direct relationship must exist with the querying object. Specifically, there must be a master-detail or lookup relationship between the two objects.
A good example of this is the relationship between Contact and Account. Here, the Contact (child) looks up to the Account (parent) via a lookup field on Contact. In this case, the lookup field name is AccountId. As a result, we can use this relationship to traverse fields on the account when querying the contact.
Another way to confirm this coupled relationship is by using Schema Builder, which visually shows ties between objects using relationship lines (master-detail and lookup).
Accessing Parent Fields: Dot Notation
Let’s dive in with an example. Here we’re going to query the child object (Contact) while pulling in an additional field from the parent object (Account):
SELECT Id, Name, AccountId, Account.Name FROM Contact
Copy and paste the above query into the Query Editor tab of the developer console and hit the Execute button. (See Part 1 if you don’t know how to navigate to the dev console.)
From the screenshot, we can see that we were able to successfully traverse the relationship and pull in the name of the account. (Remember, the account Id was already accessible as a field on the contact via AccountId, the lookup field.)
To accomplish this, we use a traversing device called dot notation. For standard fields, we use the following pattern: parent object name + (dot) + parent field name.
It’s important to point out that the above dot notation naming convention only works for standard fields. Later, I’ll show you how we deal with custom fields.
As another example, let’s say we also wanted to capture the account’s industry in our query. As we did above, we simply append the field name to Account like this: Account.Industry.
SELECT Id, Name, AccountId, Account.Name, Account.Industry FROM Contact
As you can see, now the Industry field on Account appears in each returned row. As a reminder, each row represents a contact since we’re using FROM Contact in our query — but now we’re also pulling in fields from the related account (Name and Industry).
If it helps to understand what’s happening better, you can visualize the system reading the dot notation from left to right. First, it looks at the parent object and searches for the Id via the lookup field. Then, once that parent record is found, it pulls in the corresponding field value appended with the dot (eg, Account.Industry).
On Your Own: Write a SOQL query on the Case object that also pulls in the name and rating of the corresponding account. For the case fields, include subject and status.
Traversing Custom Fields
As mentioned above, traversing to a parent object field via a custom lookup field is a little different. In these cases, we need to convert the __c suffix of the lookup field to __r (double underscore lowercase “r”) before appending the parent field name using dot notation. We can think of this “r” as denoting the relationship field that joins the two objects. But technically it stands for reference since a lookup field is a reference field type.
To start with an example, let’s say we wanted to link a contact to another contact via a custom lookup field called Spouse. Because this is a custom field, Salesforce would automatically append the __c suffix onto the field name, making it Spouse__c.
To pull the name of the contact, we would need to traverse Spouse__c using the following dot notation: Spouse__r.Name.
SELECT Name, Spouse__r.Name
FROM Contact
WHERE Spouse__c != null
The above SOQL query (in this fictitious scenery) would return both the full name of the contact and the full name of the spouse (also a contact) on all contacts where Spouse__c was not null (empty).
To make it clear that we’re actually using the lookup field name (and not the parent object name) to traverse to the parent object, I’m now going to create a new custom lookup field named after the standard object it represents. The lookup field is called Order__c, it appears on Contact, and it represents the Order object. (Remember, Order is a standard object in Salesforce — but Order is not a standard field on Contact.)
SELECT Id, Name, Order__r.Name
FROM Contact
In this fictitious query, even though we are traversing a field to access a standard object, we still must use the custom field name to access it. In other words, we’re not using the object name to traverse to the parent object: Order.Name (wrong). Instead, we must use the custom field name appended with __r: Order__r.Name (correct).
Traversing Beyond the Parent
Can we go higher up the chain while traversing? You bet! For instance, what if we also wanted to see the account owner’s name? In this case, we need to traverse the OwnerId lookup field on Account. We can write the query like this:
SELECT Id, Name, Account.Name, Account.Owner.Name
FROM Contact
Notice that when traversing beyond the parent, we need to add the additional object name and field (this adds a second dot): Account.Owner.Name. From that, we can see our pattern (for standard fields) is now parent object name + (dot) + grandparent object name + (dot) + field name. The use of “grandparent” here is in relation to the object we are querying on (in this case, Contact): Contact > Account (parent) > Owner (grandparent).
That said, it looks like we have a problem in the dev console. Look at the screenshot and tell me what’s wrong. That’s right, the dev console returns [Object Object] for Account.Owner.Name. What gives?
What gives is that the dev console doesn’t like sharing information in the query pane for any field that was traversed beyond the parent object. Why? I don’t know. But to prove that the query works as written, I’m going to share a screenshot from Workbench where the same query was run (connected to the same dev org):
And here’s a screenshot from the SOQL query editor in VSCode:
So, now that you know we can traverse multiple levels (up to 5!), I’m going to avoid traversing beyond the parent object for the rest of the article so that we can keep using the dev console with reliable results.
Finding Field Names in the Dev Console
Trying to remember field names can be difficult, especially when dealing with custom and managed package objects and fields. So, as promised in Part 1, I’m going to share a shortcut for viewing object field names directly in the dev console (as opposed to using Object Manager in Setup).
First, inside the dev console, click on the File tab and then select Open.
Next, choose Objects under the Entity Type column. Then in the filter search box at the bottom, enter the object’s name whose field names you want to see. Select the object in the Entities column (make sure it’s highlighted blue), and then click Open.
Now you should see a list of field names for your object, along with the Apex field type (see screenshot below). If you lose sight of the list (as you execute new queries and new tabs open up), simply click on its tab to reference it again (here the tab is labeled Account.obj).
While it may not seem important right now, the Apex Type column can be super helpful. For instance, I typically will click its header to arrange it alphabetically. That way it’s easy to find all the reference (lookup) fields that can be used to traverse parent objects.
If you want the field names listed alphabetically instead, do the same to the Name column — simply click the header and they will be relisted alphabetically.
Organizing by Parent Field: ORDER BY
As we saw in Part 1, you can order results using the ORDER BY clause. Here, we’re going to order first by a parent field, and then by a child field. (Yes, you can order by more than one field and object at a time! Just separate the fields by a comma after the ORDER BY statement.) To reduce crowding in the dev console, I have removed the Id fields in the following query:
SELECT Name, Account.Name, Account.Industry
FROM Contact
ORDER BY Account.Name, LastName
Looking at the screenshot above, you should see that the results were first ordered by Account.Name in alphabetic order. Then, wherever we see a repeating account name, the results are further ordered by the contact’s last name. Compare this to the previous screenshot and you’ll see the order of contact names related to Edge Communications has been reversed (now in alphabetic order by last name).
On Your Own: Update the above SOQL query so that it orders by account industry and account name. Make sure the industry is considered first and is in alphabetic order. Make sure no contacts are returned where the industry field is null.
Accessing Child Fields: SOQL Subquery
Things get a little interesting when we want to include child fields when querying the parent. For instance, let’s say we want to query accounts, but pull in all contact names for each account returned. To do this, we need to write a subquery inside our SELECT clause. Here’s an example:
SELECT Id, Name, (SELECT Name FROM Contacts)
FROM Account
Copy, paste, and execute the above query to see the results. Based on the screenshot below, a separate list of contacts (with their selected fields) is returned under the Contacts column.
If you didn’t notice, it’s super important to point out that in our subquery the object name following our FROM keyword is Contacts (plural), not Contact (singular). Specifically, this is the Child Relationship Name. To find it, go to Setup > Object Manager > select the child object (eg, Contact) > select Fields & Relationships > select the lookup field (eg, Account Name (AccountId)) > confirm the Child Relationship Name on the page. See screenshot for location:
For standard objects, this name will simply be its plural name. However, for custom objects, you will need to add __r as a suffix after the plural version of the name. For example, if we had a custom object called Invoice, its Child Relationship Name would be Invoices__r.
To illustrate this, let’s pretend Invoice__c is the child object of Opportunity. To query it using a subquery, we would write it like this:
SELECT Id, Name, (SELECT Name FROM Invoices__r)
FROM Opportunity
Don’t Forget the Comma: SOQL Parsing Error
A common mistake is to forget to include a comma before the subquery. If you forget it, you’ll get the following error: Unknown error parsing query. A comma is needed because the subquery appears in the SELECT clause and it represents an additional field.
Using WHERE in a Subquery
Whenever you write a subquery, you can use the filtering keyword WHERE. Just keep in mind that this will only filter the child field results, not the overall SOQL query.
Here’s an example where we only want to see opportunities for an account if the opportunity stage is ‘Closed Won’ and the amount is > $300,000:
SELECT Name, (SELECT Name, FORMAT(Amount)
FROM Opportunities
WHERE StageName = 'Closed Won'
AND Amount > 300000)
FROM Account
It’s worth reiterating that a WHERE clause in a child subquery only affects the child records, not the parent records returned in the main query. For instance, there are plenty of accounts returned that show no opportunities, either because none exist for that account or they didn’t meet the filter requirements. (There is a way to write the query to only return those accounts that meet the opportunity filter. This is covered under cross filters in Part 3.)
Child SOQL Query VS Child Subquery
While it’s important to know how to write a subquery to pull in child fields, often your data is more accessible and readable (as an Admin using SOQL on the fly) if you query the child object and pull in parent fields. In these cases, you should order the results by a parent field, such as name or Id, so that the child objects appear together by parent object:
SELECT AccountId, Account.Name, Name
FROM Contact
ORDER BY Account.Name
Another reason to avoid a subquery is that your child data (the child field list that’s returned) may be cut off with an ellipsis (…) if it runs off the viewing pane. This is more likely to happen when you are querying multiple fields on the child object and if there are several or more child records per list. See the query and screenshot below:
SELECT Id, Name, (SELECT Id, FirstName, LastName, Email, Phone FROM Contacts)
FROM Account
I’ve highlighted the areas where data is cut off with an ellipsis. In Apex, we have ways of accessing this data, but in the dev console’s Query Grid we can’t (no matter how much we try to expand the window or readjust the column widths).
(Ok, as we saw above, there are other SOQL query editors out there that do a better job of displaying data, even subqueries. But the Dev Console makes it super easy and convenient for beginners, so let’s stick with it for the rest of our journey!)
Using LIKE
To avoid bombarding you with too much information in Part 1, I saved the discussion for the comparative operators LIKE and IN until now. Both are used in the WHERE clause to help filter data. Not only will they supercharge your SOQL Admin superpowers, but their use is sure to impress your colleagues (ha).
Let’s take a look at the first one. LIKE, when combined with wildcard characters, is used to create a fuzzy-like match on text fields. The best way to describe it is to show an example:
SELECT Id, Name
FROM Account
WHERE Name LIKE 'United%'
As you can see, when combining LIKE with the % (percent) wildcard character, the query returned all account names that started with the word United. This is because % will return any characters that appear wherever you place it on the search term.
For instance, had we placed % on the left side of the search term (‘%United’), only account names that ended in ‘United’ would have been returned (in which case, no records would have been returned).
You can even use the % wildcard on both sides of a search term, like this:
SELECT Id, Name
FROM Account
WHERE Name LIKE '%&%'
Notice that, in addition to the United Oil account names, we now have a new row that represents Grand Hotels & Resorts Ltd since it also contains an ampersand (&).
In addition to the % wildcard, you can also use the _ (underscore) wildcard to represent a single character. For instance, if you wanted to search for all contacts with the first name Tim or Tom, you could write the following SOQL query:
SELECT Id, FirstName, LastName
FROM Contact
WHERE FirstName LIKE 'T_m'
It’s important to point out that whereas the wildcard character % will return zero or more characters, the underscore character must match one character in the position it’s placed in order to return a result (in other words, it will not return a value with a missing character in that space).
As an example, FirstName LIKE ‘Jo_n’ would return John and Joan, but not Jon.
To get all three, you could write FirstName LIKE ‘Jo%n’. And to include all first names that start with the letter ‘J’, you would use FirstName Like ‘J%’. As you can see, % is more useful than the _ wildcard.
On Your Own: Write a SOQL query that only returns contacts whose first name starts with the letter “T” or “R”. Be sure to return the Id and the full name of each contact. Run it in the dev console. Did it work?
Using IN
Moving on to the keyword IN, let’s say you’re querying accounts and you only want to see results for those accounts whose industry is transportation or energy. Typically, we would use an OR statement in the WHERE clause like this:
SELECT Name, Industry
FROM Account
WHERE Industry = 'Transportation'
OR Industry = 'Energy'
But if we want to include multiple values, or simply want to shorten things up, we can use the IN keyword:
SELECT Name, Industry
FROM Account
WHERE Industry IN ('Transportation', 'Energy')
Notice that the text values must be placed in single quotes, separated by a comma, and grouped using parentheses.
In short, this is a list. And, in the query above, the WHERE clause is asking do any of the listed values appear IN the Industry field of any account record. If so, return those records in the query results.
IN becomes far more powerful when it’s used in conjunction with a list of object Ids. For an Admin using a query editor, this will be in the form of a subquery in the WHERE clause. This will be explored later in Part 3 when cross-filters are discussed.
Using NOT IN & NOT LIKE
Can we use NOT IN? Yes — easy peasy. So, in reference to the last example above, if we want to see accounts whose industries are NOT IN the list, we write it like this:
SELECT Name, Industry
FROM Account
WHERE Industry NOT IN ('Transportation', 'Energy')
This will now only return accounts whose industries are not transportation or energy.
Keep in mind that it only makes sense to use IN or NOT IN when there are multiple values being evaluated. But when there is only one value, you should use = or != (eg, Industry != ‘Transportation’).
What about NOT LIKE? Is that a thing? Yes — sort of. Similar to what we learned in Part 1 with regard to logical operators, in this case, NOT is placed before the field name. It’s also not a bad idea to wrap a NOT clause in parentheses. Although these parentheses are only required when you use NOT alongside AND/OR, it doesn’t hurt to always use them (to avoid spending 10 minutes trying to solve a parsing error when you forget them). As an example, let’s go back to our ‘%&%’ query above. Now we’re going to pull all accounts that don’t contain an ampersand (&):
SELECT Id, Name
FROM Account
WHERE (NOT Name LIKE '%&%')
As you can see from the screenshot, none of the United Oil & Gas or Grand Hotels & Resorts accounts are listed.
SOQL Challenges: Test Your Knowledge
Ready to put your knowledge to the test? The following challenges include key concepts learned in Part 2, but also include everything covered in Part 1.
Challenge 1: Write a SOQL query that returns the name, amount, and close date of an opportunity. Also, include the account name and billing state. Only include ‘Closed Won’ opportunities. Arrange results by billing state.
Challenge 2: Edit the SOQL query from Challenge 1 so that it does not return any results where the account is located in Arizona or Texas.
Challenge 3: Write a SOQL query that includes the name of each account but also includes the name and email of each related contact. Make sure you use a subquery to pull in the child fields. Only accounts that start with the letters ‘G’ or ‘U’ should appear in your results. For each account, order the contacts alphabetically by last name. Order the account results by name in ascending order.
Don’t know where to start? I recommend building out each query in small steps. Begin with the base query in the dev console, check if it’s working, and then add on to it (bit by bit) as needed to meet the challenge requirements.
Feeling stuck? Be sure to review Part 1 and Part 2 before peeking at the answers below. Use the table of contents in each article to quickly find what you’re looking for (eg, Using LIKE & IN).
Challenge 1 Answer
SELECT Name, Amount, CloseDate, Account.Name, Account.BillingState
FROM Opportunity
WHERE StageName = 'Closed Won'
ORDER BY Account.BillingState
Challenge 2 Answer
SELECT Name, Amount, CloseDate, Account.Name, Account.BillingState
FROM Opportunity
WHERE StageName = 'Closed Won'
AND Account.BillingState NOT IN ('AZ', 'TX')
ORDER BY Account.BillingState
Challenge 3 Answer
SELECT Name, (SELECT Name, Email FROM Contacts ORDER BY LastName)
FROM Account
WHERE Name LIKE 'G%'
OR Name LIKE 'U%'
ORDER BY Name
If you were able to knock those out, you’re ready for…
Part 3: The Real Magic: Aggregate Queries (COUNT, SUM, AVG, MAX, MIN)
Do you have feedback? I’m always trying to improve this series, so be sure to leave a comment or question below if a concept wasn’t clear. Thank you!