I created an email template, component and controller that will send different set of records for each user that has a specific profile. I am having problems in my query in selecting the users that is based on opportunity owner based on task that has a status = 'Open'.
I have the following query:
User users = [SELECT Id, Name, Email FROM User WHERE Id IN (SELECT OwnerId FROM Opportunity WHERE Id IN (SELECT WhatId FROM Tasks WHERE Status = 'Open')) AND ProfileId IN (SELECT Id FROM Profile WHERE Name IN ('Project Lead', 'Project Lead - Expert'))];
What I need is to get the set of users/owner of opportunity where the opportunity's task status is set to Open.
Any help is very much appreciated. Thanks!