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

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!

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

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!)

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

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?

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.

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

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

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!

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)]