6

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?

2 Answers 2

9

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)

2

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!

1
  • 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! Commented May 7, 2014 at 13:35

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .