we have table "Contact". There are fields Id, Name and Reports To fields and so on.

The "Reports To" field is lookup to Contact.

Here, I would like to find Contact --> Contact's Supervisor ----> Contacts Supervisor's Supervirsor ---> and so on.

Can you please explain how to define this relationship using SOQL?

Best Regards
Gowtham

 we have table "Contact". There are fields Id, Name and Reports To fields and so on.

The "Reports To" field is lookup to Contact.

Here, I would like to find Contact --> Contact's Supervisor ----> Contacts Supervisor's Supervirsor ---> and so on.

Can you please explain how to define this relationship using SOQL?

Best Regards
Gowtham

Hi

Can anyone tell me how to construct the query to obtain the account hierarchy for a given account?

select Id, ParentId from Account where Id=123

this gives the parentid for this account.

How do I go from here?

Any help is appreciated.

Thanks.

The following example code demonstrates how to migrate a simple hierarchical query:

Oracle
======

SELECT "NAME", "PARENT", LEVEL
  FROM COMPANY
 START WITH ("NAME" = 'Company Ltd')
 CONNECT BY ("PARENT" = PRIOR "NAME");
 

SQL Server
==========

WITH
   h$cte AS
   (
      SELECT COMPANY.NAME, COMPANY.PARENT, 1 AS LEVEL, CAST(row_number() OVER(
         ORDER BY @@spid) AS varchar(max)) AS path
      FROM dbo.COMPANY
      WHERE ((COMPANY.NAME = 'Company Ltd'))
       UNION ALL
      SELECT COMPANY.NAME, COMPANY.PARENT, h$cte.LEVEL + 1 AS LEVEL, path + ',' + CAST(row_number() OVER(
         ORDER BY @@spid) AS varchar(max)) AS path
      FROM dbo.COMPANY, h$cte
      WHERE ((COMPANY.PARENT = h$cte.NAME))
   )
 
   SELECT h$cte.NAME, h$cte.PARENT, h$cte.LEVEL
   FROM h$cte
   ORDER BY h$cte.path
 
Note   The ROW_NUMBER() function evaluates the path column to provide Oracle nodes ordering.

Above code is for Oracle and SQL Server.

I need to implement it in SFDC using SOQL and APEX. Can some one please help!!!!

thanks