Take the 2-minute tour ×
Salesforce Stack Exchange is a question and answer site for Salesforce administrators, implementation experts, developers and anybody in-between. It's 100% free, no registration required.

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?

share|improve this question
add comment

2 Answers 2

up vote 5 down vote accepted

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)

share|improve this answer
add comment

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!

share|improve this answer
    
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 at 13:35
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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