Salesforce/SOQL - Given child, how to return “top level” parent account?

Question

I have an issue where I need to find the top-level parent account for a child. The best I've been able to do is filter up from the child w/

SELECT id, name, parent.id, parent.parent.id, parent.parent.parent.id,

 FROM Account WHERE id=CHILD_ID

Then iterate through until you find a null parent.id indicating top-level account. Not very elegant and doesn't guarantee "top-level" account, I would have preferred something like

SELECT id, name, 

    (SELECT id, name, 

     FROM Accounts WHERE id = CHILD_ID)

FROM Account WHERE parent.id = null

But that doesn't work as salesforce apparently does not let you traverse parent-child relationships from account to account. Anyone have any suggestions here?

Answer 1

There is no practical way to do this in a single SOQL query.

However, the best way to do this is as you said in the first block. You can transverse relationships up to 5 objects deep and include parent.parent.pareent.parent.parent.id in your SOQL select.

To learn in-depth about Workflow in Salesforce, sign up for an industry based Salesforce developer certification.