Salesforce coding lessons for the 99%
Finally, Apex tutorials for point-and-click admins! Written by a self-taught Google engineer.
  • Beginner Tutorials
    • Apex
    • Certifications
    • Career Info
    • Technical Architect
    • Visualforce
    • Videos
  • Apex Academy
  • Success Stories
  • About Me
  • Misc
    • Mailbag
    • Challenges
    • Links
    • Login to my Org
Follow @dvdkliuor SUBSCRIBE!

Example: How to write a cross-object SOQL query (“upwards”)

June 9, 2013

Preface: this post is part of the SOQL: A Beginner’s Guide series.

Whenever you’re traversing related objects in Salesforce, you’re always either going up or down.

Upwards traversal is used whenever there’s a lookup or master-detail field from the base object. This is sometimes referred to as going from the child to the parent.

Here are a few examples of upwards traversal:

SELECT Id, Account.Name, Account.Industry, Account.Website
FROM Contact
WHERE Account.NumberOfEmployees >= 200

The above example might look familiar to you – if you built a workflow on Contacts, you’d be able to reference the contact’s account fields by using the same dot notation.

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

You can traverse multiple levels upwards!

SELECT Id, Best_Friend__r.Likes_Ice_Cream__c FROM Contact

Here we’re traversing a custom lookup field Best_Friend__c on the contact object. Notice how the “__c” changes to a “__r” when traversing that field!

Next post: How to write a cross-object SOQL query (“downwards”)

52 Comments
Todd Kadas
July 7, 2022 @ 1:25 pm

This returns [object Object] for Account Name. Any indication why traversing upwards from opportunitylineitem to account does not return results?

SELECT
OpportunityId,
Opportunity.LastModifiedDate,
Opportunity.AccountId,
Opportunity.Account.Name,
Service_Line_Name__c,
Volume__c,
totalPrice, CM__c,
Estimated_CM__c
FROM OpportunityLineItem
WHERE Opportunity.IsClosed = false

Reply
Su
July 28, 2020 @ 11:42 am

Hi, David:

Can SOQL handle upward then downward query? I have a custom object, BP, looking up to Account, then, Account has a master-detail relationship with another custom object called Location. How do I query the BP object to the Location record? Is it possible?

Reply
    David Liu
    July 28, 2020 @ 11:50 am

    Yes it’s possible! Keep reading the guide you’ll get there!

    Reply
piyushsonijaipur
June 8, 2016 @ 2:40 am

Help in Write a basic Trigger on Opportunity ?

i want write a trigger on Opportunity,When a Opportunity is ‘close won’ after that trigger update the related account’s Custome Field Sum_of_CloseWon__C ,with the amount of closed won Opportunity ‘Amount’ Field ..
Thanks in advance. ;)

Reply
    Prassu
    October 11, 2018 @ 6:55 pm

    Hi piyush,

    Why you want a trigger to calculate sum of closed won opportunities related to that account if it is possible via Roll-up Summary field on account.
    I tried this scenario and it worked through Sum Aggregate of roll-up summary.
    Let me know if I am missing something.

    Thanks
    Prassu

    Reply
Radha Krishna N
May 25, 2016 @ 3:05 am

Hi David,
I tried to execute the following SOQL stmt in your sfdc99 Org.
Select Id, Best_Friend__r.Likes_Ice_Cream From contact

But it is giving this error.
Select Id, Best_Friend__r.Likes_Ice_Cream__c
^
ERROR at Row:1:Column:12
Didn’t understand relationship ‘Best_Friend__r’ in field path. If you are attempting to use a custom relationship, be sure to append the ‘__r’ after the custom relationship name. Please reference your WSDL or the describe call for the appropriate names.

Reply
    rk
    May 25, 2016 @ 3:18 am

    Okay. I understood that the Field API names are different in your Org. when I used the appropriate field API name it works,

    Also I observed that all your custom field’s Field API names are starting with ‘Regalaray1__’. Is there any specific reason to be used so.

    Reply
      David Liu
      May 25, 2016 @ 8:50 pm

      Someone logged into my org and registered it into that domain unfortunately…. I need to track that person down =)

      Reply
Vaishali Singh
May 18, 2016 @ 11:05 am

Hey David,
I came across a very weird behavior where in i was trying to use a field from the parent’s parent and it was not returning me any value. In fact it was giving parent’s parent id as null.This was weird because i was able to get the parent id.
I was writing an apex code in before insert operation.
for(MCPM_Interaction__c objInteraction: lstInteractions){
if(objInteraction.MCPM_Prospect__r.MCPM_PremiseID__r.MCPM_Lightning_premise__c && objInteraction.MCPM_Lightning_premise__c==false){
objInteraction.MCPM_Lightning_premise__c= true;
}
}

This not working bewildered me :(

Reply
Dan
March 25, 2016 @ 8:54 am

I’m completely stuck trying to change a specific field on a contact account, I’m not sure where to start, maybe someone here can help?

I created a process which creates a Contact record whenever someone creates a User. The Contact record mirrors the User record. However, I need to set the account name to a specific string, but I’m not sure how to do that, as whenever I trying to set AccountName to anything, it doesn’t recognize it as a field, or says I can’t go from a Contact to a User.

I’ve been programming in Apex for about a week now, so any help or direction would be much appreciated!

Love the site by the way, it’s been a huge help!

Reply
Sinan
February 5, 2016 @ 2:12 pm

Hi David,
I am just wondering if I could write a SOQL query for this:
find out list of leads that have open task assigned to specific user and where the Lead CreatedDate equals to the Task CreatedDate?

Thanks for your help, S

Reply
rahim
January 5, 2016 @ 5:35 am

How many joins write in SOQL queries like ( Child to Parent) for multiple Objects and Thair Relation ships

Reply
Anonymous
September 3, 2015 @ 11:56 pm

SELECT Id, Account.Name, Account.Industry, Account.Website
FROM Contact
WHERE Account.NumberOfEmployees >= 200

Can we write the same code in the following way:
SELECT name, industry, website, (select ID from contacts) from Account Where (NumberOfEmployees >= 200)

Reply
sinanbunni
August 10, 2015 @ 7:43 am

Hi David,

Thank you so much for your explanation to this. It really confused me everytime I look at a SOQL query to see __r at the end of custom objects. Now it is clear. Thank you for your time describing the subject in an easy way.

Reply
Anonymous
March 20, 2015 @ 3:57 pm

Hello David,
I understand the dot notation and the sub query while traversing upwards or downwards and I also understand using the Child relationship name while querying for child records from parent records. However can you tell the significance of _r and _c? When do I use each of these?

Reply
Lesa Harvey
February 10, 2015 @ 1:31 pm

Thank you, thank you, thank you! I had spent two hours sifting through Salesforce’s documentation trying to find this – and found it on your site within 2 minutes. I needed to cross and Opportunity name with and Opportunity Line item and was able to do so easily thanks to your notes! I am so excited – this opens up a whole new world of SOQL searching!

Reply
    David Liu
    February 10, 2015 @ 8:56 pm

    Yay!!!! =)

    Reply
Anonymous
December 14, 2014 @ 1:31 am

Hi,

Great tutorial on Apex.

I could not fully grasp how to determine what one can traverse. If I try to use a formula, it shows me which fields that I can traverse from another object that is “joined” to the current object as a master detail.

How to get that info in apex? Does Workbench have a way to get the proper sequence?

To be more specific, if the object employee is master detail where the detail object has a field with hours worked for the month. How to get this field? SELECT employee.detail.hoursworkedforthemonth from EMPLOYEE?

Am I making sense?

Reply
    David Liu
    December 15, 2014 @ 10:08 pm

    The only way I’ve found to simply find the exact traversal code is to use the explorer in the Eclipse Force.com IDE. But that’s a huge pain to set up!

    Luckily it always follows the exact same pattern so once you get the hang of it, you’ll be cruising forever =)

    Know that there’s two directions for traversal. If you’re getting the parent (only one possible parent), you use dot notation. If you’re getting the child (many potential children), you use a nested query. It’s one or the other so if your first guess doesn’t work try the other =)

    Reply
Raj
November 18, 2014 @ 6:38 am

Hi David,

I have question regarding process definition,Process Instance and Prcess Instance step for approval process.

My objective was to write and workflow/trigger to fire an email notification when the approval time between the steps in approval process takes more than some specific time. So, i was trying to fetch the timestamp from each step such that if the time stamp is greater than of required period then an email will be fired according step related user/queue.

To fetch the time stamp i wrote an SOQL query between the processdefinition,processinstance,processinstance step. which is

SELECT ID,(SELECT Id, (SELECT ElapsedTimeInMinutes, FROM ProcessInstanceStep) FROM ProcessInstance ) FROM ProcessDefinition WHERE Id = ‘some value’

Here for every record creation in an approval process there will be a new process instance id created keeping process definition kind of master id as reference . And for every process instance id, there will be associated step id created for approavl steps for a record in approval.

Now the question is that i an unable to understand the realtion between these 3 tables to frame my query as my query fails when it reads the subquery select.

Please let me know how can i over come this problem.

Thanks for your help
Raj

Reply
    Raj
    November 18, 2014 @ 7:37 am

    David,

    I kind of got one of the related queries with repected to process instance and its steps which is

    SELECT Id, (SELECT Id, StepStatus FROM Steps)
    FROM ProcessInstance

    However i still need to find the timestamp or field like ElapsedTimeInMinutes etc which is available in processinstancestep but not in steps.

    Reply
      David Liu
      November 18, 2014 @ 7:27 pm

      SELECT Id, ElapsedTimeInMinutes, (SELECT Id, StepStatus FROM Steps)
      FROM ProcessInstance

      =D

      Reply
        Raj
        November 18, 2014 @ 9:07 pm

        ElapsedTimeInMinutes is required for the step as i am looking for time taken for each approval step eg from step1 approavl to step2 or from step3 to step4 etc. while on the other ElapsedTimeInMinutes in ProcessInstance will give you the overall time or time taken from start of the approavl till the end or to current step in approval only. will see if i can find more data into this.

        Reply
    David Liu
    November 18, 2014 @ 7:28 pm

    You can only have one subquery for each SOQL statement!

    In this case you should break it down into two separate statements (you’ll probably need to use a Map)

    Reply
      Raj
      November 18, 2014 @ 8:34 pm

      Thanks a lot David,

      Will proceed ahead in suggested direction :)

      Reply
Sri
October 17, 2014 @ 10:46 am

Why do we get an output on SELECT Account.Owner.profile.createdby.name FROM Contact but not on SELECT Account.Owner FROM Contact

Reply
    David Liu
    October 17, 2014 @ 7:27 pm

    There is no “Owner” field on Accounts!

    There is an OwnerId field and there is an Owner.Something =)

    Reply
Radu
October 3, 2014 @ 8:12 am

Hi David,

I’m having a hard time accessing the Parent_Case__r.Owner.Name in a soql query. Any ideas why I can get the Parent_Case__r.CaseNumber but the Owner.Name just returns [object Object] values for all rows.

Reply
    David Liu
    October 4, 2014 @ 9:42 pm

    Yes – that owner of a Case can be a queue or User – so it’s confusing for the code to choose which one! There is a special syntax in SOQL to get the User owner name, but don’t remember it off the top of my head!

    Reply
Sahaj
September 25, 2014 @ 12:34 pm

Hi David,

When i am trying this query SELECT Account.Owner.Profile.CreatedBy.Name FROM Contact. I am getting this error “Parent relationship queries are disabled in Workbench: Account.Owner”
Please help

Reply
    Learning
    September 25, 2014 @ 12:42 pm

    I believe that due to the amount of processing required, the SOQL relationship queries are disabled on the Workbench. If you try it in the Data Loader it should work without issue.

    Reply
      Anonymous
      October 16, 2014 @ 1:50 pm

      Please navigate to workbench settings and enable : Allows SOQL Parent Relationship Queries

      Reply
        David Liu
        October 16, 2014 @ 8:02 pm

        Thank you anonymous hero!

        Reply
          Anonymous
          January 4, 2015 @ 11:52 am

          Hi David & Anonymous Hero –

          What is the downside of enabling this (if any)? Just wondering because of the big red error message that pops up when it is not enabled & we try & run a parent relationship query. Specifically,

          “Due to issues rendering query results, parent relationship queries are disabled by default. If you understand these limitations, parent relationship queries can be enabled under Settings. Alternatively, parent relationship queries can be run with REST Explorer under the Utilities menu without issue.”

          What exactly are the”issues” & “limitations” that we need to understand?

          Reply
            David Liu
            January 4, 2015 @ 2:25 pm

            None really as enabling it is only temporary – next time you open up workbench it’ll go back to default!

            I turn it on all the time (and have been doing so for years), no repercussions really unless you’re doing some outlandish stuff in workbench (in which case you can just turn it off)

            Reply
              Anonymous
              January 8, 2015 @ 11:13 am

              Thank you!!!! (forgot turn on “notify of new comments” or I would have thanked you sooner!!)

              Reply
hh
September 16, 2014 @ 2:35 am

SELECT Account.Owner.Profile.CreatedBy.Name FROM Contact = can this query be explained in detail means wat its displaiyng

Reply
chitral
August 16, 2014 @ 4:21 am

hi david .
SELECT name FROM Contact WHERE Best_Friend__r.Likes_Ice_Cream__c = true
with this i get name of the contact whoes best frnd likes ice cream

how can i get the name of the best frnd ? who likes ice cream
if i write
SELECT Best_Friend__c FROM Contact WHERE Best_Friend__r.Likes_Ice_Cream__c = true
it returns me the id of best frnd , but what if i want the name of that best frnd

Reply
    David Liu
    August 16, 2014 @ 3:54 pm

    SELECT Best_Friend__r.Name FROM Contact WHERE Best_Friend__r.Likes_Ice_Cream__c = true

    Reply
      Anonymous
      August 17, 2014 @ 4:17 am

      Thnkyou(:

      Reply
Rachel
August 11, 2014 @ 7:48 am

Hey David,

Do I have this write:

In dot notation, when referencing a field on a custom object, the object will be appended with __r and the field with __c?

Thanks!

Reply
    David Liu
    August 11, 2014 @ 8:32 pm

    __c for all custom fields/objects and __r when traversing custom relationships!

    So if you have a custom field “Hi”: Hi__c
    If you have a custom object “Yo”: Yo__c

    If you have a custom lookup field “Best Friend” on contacts: contact.Best_Friend__r.Hi__c

    Reply
chitral
August 9, 2014 @ 2:31 am

“SELECT Id, Best_Friend__r.Likes_Ice_Cream__c FROM Contact”
hi,
Best_Friend__r.Likes_Ice_Cream__c
it vl return the name of the best frnd who likes ice cream
is it so ??

Reply
    David Liu
    August 10, 2014 @ 4:06 pm

    Only if you have a filter:
    “Best_Friend__r.Likes_Ice_Cream__c = TRUE”

    Otherwise it’ll simply return that value whether it’s true or not!

    Reply
Tan
June 29, 2014 @ 7:45 am

David

Unclear about this one –

“SELECT Id, Best_Friend__r.Likes_Ice_Cream__c FROM Contact”

SO Best.Friend__c is a custom field on Contact. What about Likes_Ice_Cream__c ? Is this a custom field on a different object? If so which object?

Reply
    Tan
    June 29, 2014 @ 9:06 am

    think i ve got it. Best_Friend__c looks up to Contact itself. Best.Friend__r.Likes_Ice_Cream__c will return the value of Likes_Ice_Cream__c in the contact record for the value in Best_Friend__c

    Reply
      David Liu
      June 29, 2014 @ 6:00 pm

      Exactly!! Great job!!

      Reply
Srinivas
April 17, 2014 @ 11:25 pm

Hey David,
Can u please explain diff between ‘__c’ and ‘__r’??

Thanks
Srinivas

Reply
    David Liu
    April 17, 2014 @ 11:33 pm

    When using dot notation, you user __r =) Otherwise you use __c !
    https://www.sfdc99.com/2013/10/05/dot-notation-navigating-relationships-and-using-methods/

    Reply
Bernard Mesa
March 14, 2014 @ 1:09 pm

Thank you for this series! SOQL has these neat tricks that work a bit differently than the other SQL flavors.

Reply
Bart Chapman (@bartc)
February 19, 2014 @ 2:34 pm

Hi David – Thanks for ll you do!

So, your second example of the dot notation is a little unclear to me. “Account.Owner.Profile.CreatedBy.Name” Not sure if you are showing an example of the same different query as example 1, or is this a new query on a single field (the acct creator name)?

Sorry., The first example queried website, industry, acct name. Does the second one introduces different fields or a single query line for Acct Creator? Am I just being too literal?

Reply
    David Liu
    February 19, 2014 @ 10:28 pm

    Sorry it is unclear!!

    The examples are all independent. The first shows a simple, one-level traversal while the second shows that you can traverse many levels at a time! Different fields are always separated by a comma, so in the second example there is only one field being queried (Account.Owner.Profile.CreatedBy.Name)

    Hope this helps!
    David

    Reply

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *


*

*

Theme: Simple Style by Fimply