Salesforce Stack Exchange is a question and answer site for Salesforce administrators, implementation experts, developers and anybody in-between. It only takes a minute to sign up.
Sign up to join this community
I am working with a 3 Level Master Detail Object Relationship:
"Level 1" has "Level 2" detail records
"Level 2" has "Level 3" detail records
I am trying to Query "Level 1 records" and using an Outer Join include "Level 3" Records.
e.g.
SELECT Name, (SELECT Name FROM Level_2s__r.Level3s__r) FROM Level_1__c
Obviously, my notation is not correct. Is this possible or is there a better way to do this?
You are trying to traverse multiple levels in a Parent-to-Child query.
From the documentation
In each specified relationship, only one level of parent-to-child relationship can be specified in a query. For example, if the FROM clause specified Account, the SELECT clause could only specify the Contact or other objects at that level. It could not specify a child object of Contact.
You would need to break this into 2 queries in order to traverse to the Grandchild records (Level 3's)
You could try something like this:
SELECT Id, Name,
Level_2__r.Name,
Level_2__r.Level_1__r.Name
FROM Level_3__c
ORDER BY Level_2__r.Level_1__r.Name, Level_2__r.Name, Name
Will mean you'll have duplicate parents & grandparents but well... pay attention when looping through them?
A similar one in case I messed something up:
SELECT Id, Name, Account.Name, Owner.Name
FROM Contact
ORDER BY Owner.Name, Account.Name, Name
LIMIT 100
You should be careful around subqueries - sometimes they lie and the "related list" will return less records than there really are!
