Hi Experts,

 

  Could any one help me forming the below join?

 I need to retrive an User record  from User table and then get the all the users who are below this user role in the heirarchy.  

For this the logic would be: get the userroleid from USER table and read the roleid from USERROLE table where parent roleid equals the roleid from user table. by this we can get the users one step below in the heirarchy and then user all the returned roleid's as parent roleid's and get the next level of users. Like this drill down to the bottom and get all the roleid's below the user record then retrive all the userid's linked to these roleid's.

 

 

Normal SQL would be like

select roleid from userrole where parentroleid = (Select roleid from user where username like 'x%').

 

Also how to write the nested sql(get next level of roleid's in the heirarchy)?

Can any one help me writing salesforce sql for this?

 

Thanks,

Anil