+ Start a Discussion
ALAL 

SOQL query to show related account contacts in a campaign

Hello I'm looking to display a list of cases for our community users. I would like to display the cases for the contact (community user) where even though they may not be a contact on the case, they are a related contact of the account to which the case refers to.

So for Case ABC that is related to the account: Test Account, if the contact is a related contact on Test Account, then they should be able to view Case ABC. 

I tried to create a list of accounts and cases but it's not filtering filtering the cases by contact. Thank you for your help. 

String userEmail = UserInfo.getUserEmail();
        list<account> listaccounts = [SELECT ID, Name, (SELECT name,AccountId, Contact.FirstName, Contact.Email, Contact.LastName FROM Contacts Where Contact.Email != Null AND Contact.Email =: userEmail AND Contact.Email = 'guestadmin@time.com' ) FROM Account ];     
        
        list<case> myCases = [select id, casenumber, AccountId,  subject, status, priority, createddate, closeddate from case Where  AccountId =: listaccounts ];
 
Best Answer chosen by AL
GovindarajGovindaraj
Hi,

Can you give a try like below,

String userEmail = UserInfo.getUserEmail();
Set<Id> accountId = [SELECT Id FROM Account WHERE Id IN (SELECT AccountId FROM Contact WHERE Email <> NULL AND Email =: userEmail AND Email = 'guestadmin@time.com')];
List<Case> myCases = [SELECT Id, Casenumber, AccountId, Subject, Status, Priority, Createddate, Coseddate FROM Case WHERE AccountId IN : accountId]

Thanks,
Govindaraj.S

All Answers

GovindarajGovindaraj
Hi,

Can you give a try like below,

String userEmail = UserInfo.getUserEmail();
Set<Id> accountId = [SELECT Id FROM Account WHERE Id IN (SELECT AccountId FROM Contact WHERE Email <> NULL AND Email =: userEmail AND Email = 'guestadmin@time.com')];
List<Case> myCases = [SELECT Id, Casenumber, AccountId, Subject, Status, Priority, Createddate, Coseddate FROM Case WHERE AccountId IN : accountId]

Thanks,
Govindaraj.S
This was selected as the best answer
ALAL
Thank you Govindaraj. It looked like it worked.