+ Start a Discussion
Anil KakarlaAnil Kakarla 

SOQL using join

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?

SELECT UserRole.ParentRoleId FROM User WHERE UserName LIKE 'x%'

And once you have the parent role id:

SELECT Id, (SELECT Name FROM Users) FROM UserRole WHERE Id = '[parent role id]'

"Joins" are quite easy as long as a relationship exists (master-child or lookup) between objects. 


Did you manage to solve this?  I also need to programatically find all the UserRoles below a Role in the hierarchy then find all the users that are related to that role.


If you are able to post your code I would be very grateful.