function readOnly(count){ }
Sign Up ›
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
Learn More

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
Nadia GainsbourgNadia Gainsbourg 

Relationship SOQL Query: Opportunity and User

I'm trying to put together a SOQL query that would pull up the following information:

Part I:

SELECT Name, StageName, CloseDate, Amount, OwnerId,
FROM Opportunity WHERE StageName = 'Closed Won'
AND CloseDate = THIS_MONTH 

The part above works, but the problem is that I'm only getting the opportunity owner ID, not the name and I also need to limit output to the records where the owners have a specific role ID. 

The two pieces below are the pieces I need to add to the query and it seems that both of them are located on the User object. How do I write the relationship query that incorporates all three? 

Part II: 

FROM User WHERE UserRoleId = '00E0B000000zEGn'

Part III:

SELECT Username
Best Answer chosen by Nadia Gainsbourg
Ashish DevAshish Dev
Try this 
SELECT Name, StageName, CloseDate, Amount, Owner.Name, Owner.Username
FROM Opportunity WHERE (StageName = 'Closed Won'
AND CloseDate = THIS_MONTH) AND OwnerId IN (select Id from user where UserRoleId = '00E0B000000zEGn')

 

All Answers

Ashish DevAshish Dev
Try this 
SELECT Name, StageName, CloseDate, Amount, Owner.Name, Owner.Username
FROM Opportunity WHERE (StageName = 'Closed Won'
AND CloseDate = THIS_MONTH) AND OwnerId IN (select Id from user where UserRoleId = '00E0B000000zEGn')

 
This was selected as the best answer
Nadia GainsbourgNadia Gainsbourg
Hi Ashish. This has totally worked. Thank you so very much for your help!!! 
Ashish DevAshish Dev
Could you please accept my answer if it helped you.
Nadia GainsbourgNadia Gainsbourg
Now I am trying to modify the statement to select the role name associated with each record.  (and dropping the filter on user role id.)    Cannot get it to work.  I tried:

SELECT
Name, StageName, CloseDate, Amount, Owner.Name, Owner.Username, Owner.UserRoleId,
( SELECT Id, UserRole.Name FROM user )
FROM Opportunity 
WHERE (StageName = 'Closed Won' AND CloseDate = THIS_MONTH)

This didn't work.

Any help would be most appreciated.