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 (“downwards”)

June 24, 2013

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

Downwards traversal is when you’re pulling records from a related list. For example, if we’re doing a SOQL query on accounts, downwards traversal could pull data from the account’s contacts. We’ll cover this type of traversal in this post.

As a refresher, upwards traversal is the exact opposite. If you’re querying contacts, upwards traversal could pull data from the contact’s account.

Let’s take a look at a downwards traversal SOQL query:

SELECT Id, Name, Industry, AnnualRevenue,
( SELECT Name, Email, BirthDate FROM Contacts )
FROM Account

We essentially have two queries in one SOQL statement. One to pull the general account data, and one to pull the contact related list.

Don’t forget these three things when using downwards traversal:

  1. The nested query is treated like another field. That’s why there is a comma after the AnnualRevenue field.
  2. We use the plural version “Contacts” in the nested SOQL query. If you need to find the keyword for a custom relationship, find the lookup or master-detail field and look here:


    Pro tip: If you’re using custom relationships, add “__r” to the keyword: “Contacts__r”

  3. You can combine all the SOQL techniques you’ve learned into your query!
    SELECT Id, Name, Account.Description, CreatedBy.Name,
    (SELECT Amount FROM Opportunities WHERE Amount > 100),
    (SELECT Name FROM Best_Friends__r WHERE Phone != null)
    FROM Contact WHERE Account.Secret_Notes__c LIKE '%Tacos%' AND (Sex__c = 'Male' OR Likes_Ice_Cream__c = true)

This is a ridiculous query and I’m not even gonna try explaining it. Just know that you can query for some crazy things using SOQL!

Next post: Data types – strings, dates, numbers, and sObjects!

105 Comments
SL
July 11, 2022 @ 9:29 am

Hello,

How to write query when you’re pulling records where has a related list record?
for example, pull an Accounts that has at least one Contact record?

Reply
Dave C
June 24, 2021 @ 3:02 pm

David, Hi – wonderful resource. I’m super new to SOQL and have been playing around with some very simple queries which is working great but I’m stumped on this one I need to do – not sure if it all can be combined into a single statement or if I need to break it into 2 (which both work today, but I’m trying to get as elegant as possible – :) )

So, I can pull detail from an Account record like so:

SELECT Account.Name, Account.Support_Type__c, Account.Assigned_CSM_2__c, Account.TAM_CSM_Assignment__c from Account where Id = ‘xxxxxxxxxxxxxxx’

That works fine, but the Account.TAM_CSM_Assignment__c is a User ID from which I can get the User.Name and User.Email of that user via:

SELECT User.Name, User.Email from User where Id = ”

But, what I’d like to do is see if it’s possible to do both of these with a single query – possible?

Thanks!

Reply
    Dave C
    June 25, 2021 @ 11:16 am

    One correction to what I posted above the second SELECT should read:

    SELECT User.Name, User.Email from User where Id = ‘xxxxxxxxxxx’

    where xxxxxxxxxxx is the value returned by the Account.TAM_CSM_Assignment__c query above.

    Thanks!

    Reply
Adeela
March 29, 2021 @ 3:48 am

Best resource for me to understand the cross object query. I was getting so frustrated as my query wasn’t working but after going through this page I was able to formulate the query and got the records I was expecting.
Thanks a lot David!

Reply
Mouna
June 12, 2019 @ 9:49 am

Hello David,

I am just readinG your blog about how to write a cross two object on soql query because I have to do an extraction on Contact and Account object. You will find bellow my request based on your blog :

SELECT Id, MDM_Id__c, OwnerId,
( SELECT Email, Id, MDM_Id__c, Salutation, FirstName, LastName, OwnerId, Name, AccountId FROM Contacts) FROM Account

1) I have put an “s” on Contact object but it is strange… When I click on “list”, I see all my data but I can not bulk CSV, I have this error message : InvalidJob: Unable to find object: Contacts
2) So, I deleted the letter “s” on Contact (because the contact object has not an “s”), instead of it does not working when I click on “List” or “BULK CSV”. I have this error message :

Error message from List

Error message from BULK csv

Could you please help me please ?????

Thank so much in advance for your help

Reply
    Imo J
    September 23, 2020 @ 5:18 am

    Hi Mouna,
    I am having the same issue, did you find something how we can download the CSV file from the workbench?
    Thanks

    Reply
Mouna
June 12, 2019 @ 9:45 am

Hello David,

I am just readinG your blog about how to write a cross two object on soql query because I have to do an extraction on Contact and Account object. You will find bellow my request based on your blog :

SELECT Id, MDM_Id__c, OwnerId,
( SELECT Email, Id, MDM_Id__c, Salutation, FirstName, LastName, OwnerId, Name, AccountId FROM Contacts) FROM Account

1) I have put an “s” on Contact object but it is strange… When I click on “list”, I see all my data but I can not bulk CSV, I have this error message : InvalidJob: Unable to find object: Contacts
2) So, I deleted the letter “s” on Contact (because the contact object has not an “s”), instead of it does not working when I click on “List” or “BULK CSV”. I have this error message :

Error message from List

Error message from BULK csv

Could you please help me please ?????

Nb : I am added you on linked , my name is hannachi mouna

Thank so much in advance for your help

Reply
Pravin
April 12, 2019 @ 9:40 am

Hi David,

I have a parent custom object(Team__c) to the Contact object with Lookup relationship

I need a SOQL query to retrieve the records from Team__c Custom object which at least have one contact associated with it.

I know we can do this via APEX, but just checking Is there a direct way in SOQL?

Thanks in advance

Reply
    David Liu
    April 15, 2019 @ 11:10 pm

    The “downwards” query on this post is what you want!

    Reply
Tanu
March 5, 2019 @ 12:45 am

Hi David,

I have a query on the SOQL eg that you have provided –
[SELECT Id, Name, Account.Description, CreatedBy.Name,
(SELECT Amount FROM Opportunities WHERE Amount > 100),
(SELECT Name FROM Best_Friends__r WHERE Phone != null)
FROM Contact
WHERE Account.Secret_Notes__c LIKE ‘%Tacos%’
AND (Sex__c = ‘Male’ OR Likes_Ice_Cream__c = true) ]

My question is how is this query handled if we get more than one value returning from the lines –
(SELECT Amount FROM Opportunities WHERE Amount > 100),
(SELECT Name FROM Best_Friends__r WHERE Phone != null)

Reply
    David Liu
    March 5, 2019 @ 7:17 am

    Most queries will return multiple lines. Keep reading the next posts in the chapters to see what happens! Basically the results are a list

    Reply
Jay
December 21, 2018 @ 12:44 am

Hi David,

Thanks so much for this! I wanted to ask though, how do you find if a contact has related lists and, find out what those lists are?

Reply
Akhilesh
October 29, 2018 @ 9:24 pm

Hi ,

I am new to SOQL and have written the below query and this is not returning data for ‘contact.Name’

“select id,contact.id,contact.Name,account.id from case”

I have also tried

“select id,(select Name from contacts) from case”

and both are not working.

Please help on this.

Thanks.

Reply
    Prassu
    October 31, 2018 @ 4:40 am

    Hi Akhilesh,

    You need to use the below query to retrieve contact name on case

    Select case.contact.name from case

    Reply
Joaquín
April 26, 2018 @ 1:48 pm

Hi!

I’m trying to run a query to check if records from a Custom Object, don’t have an associated account (account is a related list for this custom object). I know that the easiest way would be to create a rollup field in the custom object and check for when it’s zero, but as it’s a one time thing I’m trying to do it with a SOQL query.

Is there any way to do this? Any help would be great!

Thanks a lot! :)

Reply
    Prassu
    October 31, 2018 @ 4:49 am

    SELECT Name FROM Custom_Object__c WHERE Custom_Object__c_Account__c = null

    I created a lookup relationship between custom object and account. Then tried to track if there is related account associated with my custom object.

    Understood this way but let me know if question is different.

    Reply
Deepshikha Shriwas
April 18, 2018 @ 12:42 pm

Hi David,

So if i want to get the count of opportunity line items on account then we should write 2 different soql queries correct?
Also the recalculation should take place whenever we try to delete/add/update the opportunity line items.

Thanks in advance!

Reply
    David Liu
    April 18, 2018 @ 10:56 pm

    There’s a few ways to do this properly! You might be able to do this with one SOQL query but two is fine too =)

    Reply
Deepshikha Shriwas
February 19, 2018 @ 5:19 am

Hi David,

I am trying to query User login history object and check for login attempts with status= invalid password.
Can i do this?
I tried but it using the query below in the query editor:
SELECT Id, UserId, LoginTime, LoginType, Browser, Status FROM LoginHistory where Status = ‘Invalid Password’

But i get an error that:
Status FROM LoginHistory where Status = ‘Invalid Password’ ^ ERROR at Row:1:Column:82 field ‘Status’ can not be filtered in a query call

Why is that so..how should i query this?

Kindly let me know

Reply
    David Liu
    February 24, 2018 @ 7:32 pm

    Guess you gotta remove that filter =) Just one of those random Salesforce limits!

    Reply
swati
November 2, 2017 @ 12:57 pm

[SELECT FirstName,LastName,Email, MobilePhone,You_are_intrested_in__c,Lead_Type_del__c, Courses__c
(SELECT Payment_status__c,Payment_Offline__c,Mode_Of_Payment__c FROM Payment__c where Payment_status__c=’Done’ )FROM Lead];

Reply
    Anonymous
    November 2, 2017 @ 12:58 pm

    Please can anyone help me

    Reply
      Nikhil
      December 7, 2017 @ 1:23 am

      She is trying to get all the account details (or customer details) who has “Done” the payment.

      Reply
Sarah
December 14, 2016 @ 9:31 am

Hi there! I am trying to query the relationship between Product2 and OpportunityLineItems. So far I’ve found out that there is a Pricebook Entry in between those two, but I haven’t been able to figure out querying down multiple levels.
Any hints? (Awesome site btw, so helpful!)

Reply
    David Liu
    December 19, 2016 @ 10:36 pm

    You should be able to get to the product from the opportunity line item

    Reply
Salesforce geek
October 23, 2016 @ 2:22 am

Hi
How can I query the profile names of opportunity owner , somewhat like this :
select id, profile.name, (select id from opportunities) from user

-but we do not have a “child relationship name” on owner lookup field on opportunity (i have used ‘opportunities’ which is invalid)

Reply
    David Liu
    October 23, 2016 @ 8:41 pm

    Try querying the opp object and doing Owner.Profile.Name instead!

    Reply
Prasanjeet
September 7, 2016 @ 5:01 am

Hi David,

while executing this query :

SELECT Id, Name, Account.Description, CreatedBy.Name,
(SELECT Amount FROM Opportunities WHERE Amount > 100),
(SELECT Name FROM regalray1__Best_Friend__r WHERE Phone != null)
FROM Contact
WHERE Account.Secret_Notes__c LIKE ‘%Tacos%’
AND (Sex__c = ‘Male’ OR Likes_Ice_Cream__c = true)

I am getting this error :

(SELECT Name FROM regalray1__Best_Friend__r WHERE
^
ERROR at Row:3:Column:22
Didn’t understand relationship ‘regalray1__Best_Friend__r’ in FROM part of query call. 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.

Could you please explain .

Thanks

Reply
    David Liu
    September 7, 2016 @ 7:34 pm

    The relationship name for that one is “Best_Friends” – you can find this out by navigating to that field and looking at its description!

    Reply
Terry Miller
August 30, 2016 @ 2:28 pm

God bless you! I’ve spent hours on a query that I knew should work and could not figure out way. Thank you for making it clear that we have to use the “Child Relationship Name”.

Reply
    David Liu
    August 30, 2016 @ 7:20 pm

    My pleasure Terry!

    Reply
jagdish nikam
August 15, 2016 @ 1:59 pm

When I execute the query “select name, industry, (select email from contacts)from Account where name=’Burlington Textiles Corp of America” i got output : (Account:{Name=Burlington Textiles Corp of America, Industry=Apparel, Id=0012800000ehtcaAAA}). Why i am not getting email of contact present in the account. I am new to sfdc. Could anyone suggest?

Reply
    David Liu
    August 15, 2016 @ 2:15 pm

    There might be no contacts on that account

    Reply
Ansh
June 15, 2016 @ 10:37 pm

Why this query is not running
SELECT Id, Name, Industry, AnnualRevenue,
( SELECT Name FROM cases)
FROM Account

Reply
    David Liu
    June 15, 2016 @ 10:50 pm

    Post the error message!

    Reply
      Anonymous
      August 26, 2016 @ 4:41 am

      select id,name,Industry,
      (Select Name,Email from contacts)
      from acccount

      error is
      Name,Email from contacts) from acccount
      ^
      ERROR at Row:1:Column:64
      sObject type ‘acccount’ is not supported. If you are attempting to use a custom object, be sure to append the ‘__c’ after the entity name. Please reference your WSDL or the describe call for the appropriate names.

      Reply
        Anonymous
        September 23, 2016 @ 4:25 pm

        ‘acccount’ – with 3 ‘c’ characters?

        Reply
        lily
        April 25, 2018 @ 7:23 pm

        Triple ‘c’ in acccount

        Reply
Anonymous
June 14, 2016 @ 11:59 am

Great examples, Have watched all your dreamforce videos and this site.. everything is inspiring!!

Reply
    David Liu
    June 14, 2016 @ 5:48 pm

    Thanks!

    Reply
Anonymous
May 18, 2016 @ 11:17 am

hi David,

Reply
    David Liu
    May 19, 2016 @ 11:47 pm

    Hi!

    Reply
Leandro Valdes Viera
October 9, 2015 @ 1:59 pm

Is this the “query equivalent” of creating a report and using cross-filters with the related objects? Thanks!

Reply
    David Liu
    October 14, 2015 @ 8:21 pm

    Pretty much!!!

    Reply
sanju
July 14, 2015 @ 5:43 am

hi David,

I understood the concept of retrieving the related list data from its parent object , But didnt get how to access the related list records in that query result.

For ex :List acclist = SELECT id, count_contacts__c, (SELECT id, name FROM contacts) FROM Account WHERE id =’0019000001NQXWcAAP’;

how to access the child records.For ex : contacts

Reply
    David Liu
    July 14, 2015 @ 3:05 pm

    Say your account record variable (not the list of accounts) is acc. You’ll access the contacts via acc.Contacts

    Reply
      sanju
      July 15, 2015 @ 3:33 am

      Thanks David . Thats anwered my query. Ur a champ!!!!!

      Reply
radha
February 14, 2015 @ 12:50 pm

festlist = [select ID,(select ID,attendie_number_for_festival__c from attendy__r) from festival__c];
this is the error
Didn’t understand relationship ‘attendy__r’ in FROM part of query call. 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
    radha
    February 15, 2015 @ 12:18 pm

    sorry ignore this doubt i got it

    Reply
Peter
January 29, 2015 @ 1:05 pm

Hi David!

Great post! How would l build a SOQL query that would give me the email address of parents with 2nd graders? I am using accounts as households and have contacts related with head of household, spouse, child (if child, separate field tracks their grade).

Thanks!

Peter

Reply
    David Liu
    January 29, 2015 @ 10:18 pm

    I think I’m going to make a challenge out of this!

    One solution is to query all 2nd graders, loop through them, and add all their parent’s email addresses to a set. Maybe there is a more efficient way of doing it though =)

    David

    Reply
      Peter
      February 9, 2015 @ 2:40 pm

      Thanks David! I’m new to your site (which is great, by the way), what do you mean by challenge and where can I find this?

      Thanks!
      Peter

      Reply
        David Liu
        February 9, 2015 @ 6:43 pm

        I was going to make it a public challenge for others to try to solve =)

        https://www.sfdc99.com/challenges/

        Reply
Aron Schor
January 21, 2015 @ 7:56 pm

Hi David,

Maybe you could do a post on advanced SOQL?

->Possible general ideas

count(id) (Saw on a blog, but not sure how to use and when.)
sum(pid= ) (Saw on a blog, but not sure how to use and when.)
union all (Saw on a blog, but not sure how to use and when.)
“AND id IN” (I have used.)
GROUP BY ROLLUP (I have used, but not exactly how I want.)
Is addition/subtraction possible?

->Possible specific examples (the first three I can’t solve)

***Not asking for help solving, just want to take my SOQL to the next level!***

1 Accounts that only purchase products belonging to select Product Families (or have not purchased)
https://developer.salesforce.com/forums/ForumsMain?id=906F0000000AvuYIAS

2 QTY purchased per Product for a specific Account
https://developer.salesforce.com/forums/ForumsMain?id=906F0000000Av7lIAC

3 Orders shipped (invoiced) 5 days or more after the order was entered.
Tried using addition/subtraction with no luck, ie WHERE Order_Date__C = 2014-12-15 AND Order_Date__C <= 2014-12-19
AND id IN (select Order_header__c FROM Order_Detail__c WHERE Warehouse__C = '57')
ORDER BY ORDER_DATE__C

Reply
    David Liu
    January 21, 2015 @ 7:59 pm

    Coming up in Chapter 10!

    To be honest, even with many years of coding I’ve never needed to use some of the crazier things, like group by rollup. I will cover the ones that you’ll likely see like SUM, COUNT, GROUP BY, dynamic, etc though =)

    Reply
Nick
January 2, 2015 @ 1:47 am

Hi David,

I am trying to delete contacts in bulk. However, i am not able to delete contacts which are associated with cases.
Can you help me with a query to find out contacts not related to any case.

Reply
    David Liu
    January 2, 2015 @ 9:10 am

    Try posting this one on the forums =) Great beginner question!

    Reply
    Josh E.
    February 24, 2015 @ 1:55 pm

    Hi Nick –

    How about this:

    *******
    //1. Query all Cases in system
    List query = [SELECT Id, CaseNumber, ContactId
    FROM Case];

    System.debug(query);

    //2. Create a Set to store Id’s called “contactIdsInCases”, cycle thru the query from #1 above, and insert Case Contact Id’s into “contactIdsInCases”.
    Set contactIdsInCases = new Set();
    For (Case c: query){
    contactIdsInCases.add(c.ContactId);
    }

    //3. Create a List of all Contacts who aren’t in the “contactIdsInCases” Set above from #2.
    List contactsWNoCases = [SELECT Id, Name
    FROM Contact
    WHERE Id NOT IN :contactIdsInCases];

    System.debug(contactsWNoCases);

    Reply
      Josh E.
      February 24, 2015 @ 1:58 pm

      Hmm….my code looks different from what I typed.

      Looks like anything between a less than () sign disappeared. So in other words, it’s not clear what type of object is in my collections (Case, Id, Contact).

      Reply
Aron Schor
December 8, 2014 @ 8:11 am

Hi David, Your blog has helped teach me the very basics of coding. I have been reading your site for about a week and have “Created” some SOQL queries based on your examples and tips. Two questions. I can’t figure out how to determine relationships. I assume I need to do that see Sales Order Lines tied to a specific account. How do I do these things?

Maybe this information helps: If I go to Order_Detail__C (Object) I see Order_Header_C (Field.) If I go to Order_Header__C (Object) I see Syspro_AccountId__C (Field) which I also see in Account (Object.)

This also might help. I came up with this query and it said Parent Relationships Disabled In Workbench. I fixed it, and now it says “Bind variables only allowed in Apex code”

SELECT Syspro_AccountId__c, NAME, ( SELECT NAME, Line_Value__C, Product_ID__c FROM Order_Detail__c WHERE NAME LIKE = ‘US.8001850%’, Line_Value__C < 5 ) FROM Account WHERE Syspro_AccountId__c LIKE 'US.8001850%' Thanks for any suggestions. Aron

Reply
    David Liu
    December 8, 2014 @ 7:42 pm

    Change this:
    LIKE = ‘US.8001850%’, Line_Value__C < 5 To this: LIKE = ‘US.8001850%’ AND Line_Value__C < 5 Then you should be good to go =)

    Reply
      Aron Schor
      December 8, 2014 @ 7:57 pm

      No, same issue. I tried
      SELECT Syspro_AccountId__c, NAME, ( SELECT NAME, Line_Value__C, Product_ID__c FROM Order_Detail__c WHERE NAME LIKE = ‘US.8001850%’ AND Line_Value__C < 5 ) FROM Account WHERE Syspro_AccountId__c LIKE 'US.8001850%'
      and it still says
      MALFORMED_QUERY:
      Order_Detail__c WHERE NAME LIKE = 'US.8001850%' AND Line_Value__C
      ^
      ERROR at Row:1:Column:115
      Bind variables only allowed in Apex code Any other ideas?

      Reply
        David Liu
        December 8, 2014 @ 8:11 pm

        OH make sure to check out bullet #2 in this post and choose the Child Relationship Name instead!

        Note that after finding the child relationship name, you need to make it a __r instead of __c =)

        Reply
          Aron Schor
          December 9, 2014 @ 2:58 pm

          Thanks David, still having problems determining this info. Let me see if I can do some more digging on this but I appreciate the guidance!

          Reply
            Aron Schor
            December 16, 2014 @ 2:05 pm

            Hi David-I am still struggling with this. Let me post extra information. If you have time hopefully you can make sense and help me with the query or possibly this isn’t something that I can do. I am trying to tie the Rep directors with products to see who selling more of select items. I think the relationship issue is confusing or maybe a bit advanced for me.

            SOME INFORMATION:

            If I go to Standard & Custom Objects

            1 Account-it says this under Child Relationships:
            Order_Header__c.Account__c
            -cascadeDelete: true
            -childSObject: Order_Header__c
            -deprecatedAndHidden: false
            -field: Account__c
            -relationshipName: Orders__r

            For Account-it says this under Fields:
            Rep_Director__c

            2 Order_Header__c-it says this under Child Relationships:
            Order_Detail__c.Order_Header__c
            -cascadeDelete: true
            -childSObject: Order_Detail__c
            -deprecatedAndHidden: false
            -field: Order_Header__c
            -relationshipName: Order_Detail__r

            3 Order_Detail__C-It says this under Fields
            Order_Header__c

            I HAVE TRIED A FEW VARIATIONS WITH NO LUCK:

            1
            SELECT Rep_Director__C, ( SELECT Description__c, Order_Quantity__c, Stock_Code__c FROM Order_Detail__C WHERE Stock_Code__C = ‘6195’ ) FROM Order_Header__r.Account__c
            Error-“First SObject of the FROM statement must be a valid SObject type.”

            2
            SELECT Rep_Director__C, ( SELECT Description__c, Order_Quantity__c, Stock_Code__c FROM Order_Detail__C WHERE Stock_Code__C LIKE = ‘6195’ ) from Order_Header__C
            Error-“Bind variables only allowed in Apex code”

            3
            SELECT Description__c, Order_Header__C, Order_Quantity__c, Stock_Code__c, ( SELECT Rep_Director__C from Order_Detail__c.Order_Header__c ) FROM Order_Detail__C WHERE Stock_Code__C LIKE ‘6195’

            “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.”

            When I change the settings it says
            “Didn’t understand relationship ‘Order_Detail__c.Order_Header__c’ in FROM part of query call. 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.”

            Thanks so much!

            Reply
              David Liu
              December 16, 2014 @ 7:30 pm

              Hhmm let’s see how I can help!

              I think it’s a lot more confusing if you look at the field definitions from your UI vs. the normal Salesforce website.

              Look at it this way. You have an Order_Header__c object and an Account object. The two are related, which means a lookup/master-detail field exists on the Order_Header__c object pointing to the Account, or vice versa.

              If the lookup field is on the Order_Header__c object, this means if you’re doing a SOQL query on Order_Header__c, you use dot notation to get Account fields. IE SELECT xyz, Account__r.Name FROM Order_Header__c. If you’re doing a SOQL query on Account, you use a nested query IE SELECT Name, (SELECT Order_Field_1__c FROM Order_Headers__r) FROM Account

              Note two things:
              1. You can’t use dot notation in an object that’s immediately after the “FROM”
              2. You use __r in nested SOQL queries. See the post for more details on this!

              Hope this helps! Don’t give up!
              David

              Reply
                Aron Schor
                December 20, 2014 @ 2:13 pm

                Ha, I like your comment “I think it’s a lot more confusing if you look at the field definitions from your UI vs. the normal Salesforce website.” Everything about my SalesForce background is confusing! I don’t think I can see this via the normal website as I am not an admin, atleast I can’t see the page shown in the image on this page. I have never been formally trained with SalesForce (just given an id and pw) and I am not sure exactly what I can and can’t do since I am not an admin. We don’t have a traditional admin at our company, as the person with access has other responsibilities, though hopefully that changes in the next year. SalesForce is not yet a priority. I also have no programming background. You basically have taught me everything I know!

                I gave up but I posted in the Success and Developer communities. I was told this couldn’t be done by someone in Success but someone in the Developer helped me out.
                SELECT Description__c, Order_Header__C, Order_Quantity__c, Stock_Code__c,Order_Header__r.Rep_Director__C FROM Order_Detail__C WHERE Stock_Code__C LIKE ‘%6195%’

                I had never seen a comma used in the code before “Stock_Code__c,Order_Header__r.Rep_Director__C” Never would have figured it out. Thanks for the help!
                https://developer.salesforce.com/forums/ForumsMain?id=906F0000000AtSlIAK
                https://success.salesforce.com/answers?id=90630000000CmaZAAS

                Reply
                  David Liu
                  December 20, 2014 @ 2:20 pm

                  hahaha the guy who helped you is a BALLER!!

                  You definitely have the curiosity to become a great admin/dev, keep up the good work and you will make Salesforce a priority at your company!

                  Reply
                radha
                February 14, 2015 @ 12:44 pm

                Account is a standard object then why Account__r?

                Reply
                  David Liu
                  February 14, 2015 @ 2:52 pm

                  It’s a custom field/relationship!

                  Reply
                    radha
                    February 15, 2015 @ 12:36 pm

                    got it ,just confused
                    thank u so much

          Anonymous
          April 10, 2015 @ 8:22 am

          Sorry, just saw this while trying to write a downward SOQL. I think your “Order_Detail__c” should be “Order_Details__r”.

          Reply
Christine
November 27, 2014 @ 11:21 am

Can you do something like this with User and UserLogin?

Reply
    David Liu
    November 28, 2014 @ 9:42 pm

    You sure can =)

    In fact, you’d be hard pressed to find something you CAN’T SOQL query!

    Reply
      Arnt
      January 30, 2015 @ 1:43 am

      I’m also wondering about this, I can’t make this work for User and UserLogin:
      select Id, IsFrozen, IsPasswordLocked, UserId, (Select Email from Users) from UserLogin
      select Id, IsFrozen, IsPasswordLocked, UserId, User.Email from UserLogin
      etc etc, nothing works, it all says it doesn’t understand the relationship ‘User’, event though the UserId field is documented as type reference to, just ike account is for contact. What am I missing?

      Reply
        David Liu
        January 30, 2015 @ 8:31 am

        Strange!

        Maybe try the other way – base query on User

        Reply
Joe V
November 11, 2014 @ 7:34 am

Is it possible to do a downward query from a parent object to a grandchild? I have 2 Master-Detail branches to query. On one branch I need the child object, but on the other I need to look 2 levels down. Appreciate any help. Great series of tutorials here!

Reply
    David Liu
    November 11, 2014 @ 10:02 am

    This is possible with two separate SOQL statements (you can’t go downwards two levels in one SOQL)

    Here’s an example:
    Account –> Opportunities –> Opportunity Contact Roles

    You can’t do this: [SELECT Id, (SELECT Amount, (SELECT Id FROM OpportunityContactRoles) FROM Opportunities) FROM Account];

    But you can do this: [SELECT Id, (SELECT Amount FROM Opportunities) FROM Account];
    [SELECT Id, (SELECT Id FROM OpportunityContactRoles) FROM Opportunity WHERE Id IN :myOppList];

    Reply
      radha
      February 13, 2015 @ 11:55 am

      so, can we move downwards to any level using the second approach and also is there any limitation for upward traversal also?

      Reply
        David Liu
        February 13, 2015 @ 9:28 pm

        You are 100% correct =) The limitations going upwards are very generous, something like 5 levels.

        Reply
          radha
          February 13, 2015 @ 10:34 pm

          thank u , I am really happy that i found this site.

          Reply
      Monika
      March 19, 2015 @ 11:10 pm

      Very well explained David. Hugely appreciate the guidance !

      Reply
Dharmendra
October 9, 2014 @ 5:15 am

David is the Ultimate Saviour!!!!

Reply
Sam Gladstone
October 2, 2014 @ 5:07 pm

David,

Thank you so much for this fantastic, concise tutorial. This easily just saved me who-knows-how-much time crawling through SalesForce documentation to find the straightforward solution to a straightforward problem. I hugely appreciate your having taken the time to write this out. Thanks again!

Reply
    David Liu
    October 2, 2014 @ 8:01 pm

    No probs!

    Reply
Anonymous
September 26, 2014 @ 3:00 am

DAVID,
CAN U PLZ tell..how to fetch data from a object field to a another object field every time data is inserted…like if there is a cuctom object ..FACT…and in this object there is a field NAMED….. TOTAL FACT…IF WE WANT TO populate this field every time record is created from a field values on a different object…..how will we accomplish this/…???

Reply
Anonymous
September 23, 2014 @ 5:16 am

David you are AWESOME!!!
Can’t wait for your next chapters!
please do quickly….

Reply
Anonymous
August 14, 2014 @ 2:32 am

how to write query to get account name from quote object and product name from relative quote line item???? can you help me…i got stucked here??????????

Reply
    David Liu
    August 14, 2014 @ 9:44 pm

    So the traversal goes like this:

    Quote Line Item >> Quote >> Opportunity >> Account
    Quote Line Item >> Product2

    All upwards SOQL queries, check this post out!
    https://www.sfdc99.com/2013/06/09/example-how-to-write-a-cross-object-soql-query/

    Reply
Nitin
July 3, 2014 @ 7:44 am

Hi David,

How to fetch/access/retreive the values “Amount” & “Name” (double quoted) in the nested queries of the below query for comparison purpose. Like if we need to compare the Amount returned is equals 200 or something else ?

List abc = [SELECT Id, Name, Account.Description, CreatedBy.Name,
(SELECT “Amount” FROM Opportunities WHERE Amount > 100),
(SELECT “Name” FROM Best_Friends__r WHERE Phone != null)
FROM Contact
WHERE Account.Secret_Notes__c LIKE ‘%Tacos%’
AND (Sex__c = ‘Male’ OR Likes_Ice_Cream__c = true)]

Reply
    Nitin
    July 3, 2014 @ 7:47 am

    abc is List of Contact which has been erased by this page formatting rule

    Reply
    David Liu
    July 3, 2014 @ 10:40 am

    What you have there actually looks good! (Except for the double quotes!)

    Then you’d do…

    for (Contact c : abc) {
      for (Opportunity o : c.Opportunities) {
        Integer amt = o.Amount;
        // …
      }
    }

    Reply
      Nitin
      July 4, 2014 @ 12:42 am

      Thank you very much Dave … you are awesome man!!!!

      Reply
        Nitin
        July 4, 2014 @ 12:48 am

        I have intentionally added those double quotes to highlight the content. so that you can spot it easily :)

        Reply
Anne
June 13, 2014 @ 11:45 am

David,
i am trying to write some cross object SOQL in the workbench and i am struggling with getting it to recognize the relationships. I am trying to pull fields from the lead and from the campaign member i have tried both upwards and downwards queries (i am pretty sure it is a downward) but still no luck. I also tried to write a a query based on some custom objects i have in production and the relationship was not recognized

Any help would be greatly appreciated, what i have is below

SELECT Id, Lastname, createddate,
(SELECT status, FirstRespondedDate FROM CampaignMembers__r)
FROM Lead

Reply
    David Liu
    June 17, 2014 @ 6:19 pm

    You are right, you need a downwards query!

    You only need to use __r for custom relationships, since Campaign Members are standard objects you can exclude that =)

    So this query is the one you’re looking for!
    SELECT Id, Lastname, createddate,
    (SELECT status, FirstRespondedDate FROM CampaignMembers)
    FROM Lead

    David

    Reply
      Anne
      June 18, 2014 @ 8:41 am

      THANK YOU – that did the trick when i am hero of leads i will give you credit. So because the relationship is native to sales force vs a relationship i created i don’t need __r or __c but if it was a custom object i would need __r or if it was a Anne made relationship i would need __c – i am having trouble getting my head around when i need these things

      Reply
        David Liu
        June 18, 2014 @ 9:37 pm

        You are 100% right Anne!

        Anne made relationships = __r
        Native relationship = no __r

        Reply
Anonymous
April 10, 2014 @ 4:02 am

Hi,

U have given very good information. I like it the way it is given.

Thanks
Swapnil

Reply
Ales
April 5, 2014 @ 5:21 pm

Hi David,
Thank you so much for your site. I’ve been gone for a while, but am back again and determined to stick to this. Your site is awesome!!!

In your query (SELECT Name FROM Best_Friends__r WHERE Phone != null) – should it be Best_Friends__c? Just wondering.

Thank you again!

Reply
    David Liu
    April 5, 2014 @ 6:29 pm

    Welcome back, it’s great to have you!! Lots of new content up on the site to get you to a point where you can get a job as a dev!

    The __r syntax is correct in this case since it’s a “nested” query inside the larger Contact query. The “__r” stands for “related” since it’s querying Best Friends related to each Contact in the outer query. If we were simply querying the Best Friend object and nothing else, we’d use __c!

    Reply
      Ales
      April 6, 2014 @ 2:50 pm

      Hi David,
      Thank you for clarifying this. Appreciate it! Keep up the good work. I did notice all the new contents, including the videos – love them. Awesome work!!!

      Reply
Faraz Khan
September 17, 2013 @ 5:37 am

Hi David,

Awesome series you are doing here.. still waiting on you to post more tutorials.
Any info on when to expect them?

Reply
Matt Santy
August 22, 2013 @ 7:14 am

Why aren’t you posting anymore? This series is really well done and helpful.

Reply
    David Liu
    August 22, 2013 @ 11:56 pm

    Matt – happy to see people are getting value from this site! There are so many tutorials in my mind that I must do – stay tuned!

    Reply

Leave a Reply Cancel reply

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


*

*

Theme: Simple Style by Fimply