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.


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.

See Here

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,
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

You should be careful around subqueries - sometimes they lie and the "related list" will return less records than there really are!

  • Yes, I could do that. I am trying to specifically use an Outer Join to take advantage of some functionality in SFDC document merge app Conga Composer. Long story short... The merge tool only allow for 1 level of Grouping in the Output. You can get around this limit adding a second grouping level with an Outer Join. Thanks! – Justin J Carlson May 7 '14 at 13:35

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.