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
Support 3743Support 3743 

Selecting all of the contacts that have opportunities

Hi, I want to select from Contacts all of the contacts that have opportunities. I used the code below but it's not working:

SELECT Name, (SELECT Name FROM Opportunities) FROM Contact WHERE AccountId in (SELECT AccountId FROM Opportunity)

I know how to select through Account, but I want to know how to do it this way. Thank you!
Rahul.MishraRahul.Mishra
Your query seems to be coorect and iquerying  the records from all the contact records with additional filter , where AccountId  = AccountId related to the child opportunities of your contacts.
 
Support 3743Support 3743
@Rahul.Mishra, I executed the code but the query result displays all the opportunities that are blank, rather than returning opportunities with contents. I assume only opportunities that exist have an AccountId?
Rahul.MishraRahul.Mishra
Hi,

Query returns all the contacts where Opportunity.Account = Contact.Account. You are getting opportunity information as null because those opportunity do not have associated to contact, I mean opportunities are associated to contact using Contact Role object. You try create a Contact Role on any of the opportunity with contact, you will receive that opportunity in result after you run the query:

Check the link for Contact Role relationship : https://developer.salesforce.com/docs/atlas.en-us.api.meta/api/sforce_api_objects_opportunitycontactrole.htm


Mark solve if it does help you.
Support 3743Support 3743
@Rahul.Mishra, it's me again! I want to select all of the accounts whose contacts have an email address, but my query is not working:
SELECT Account.Name, (SELECT Name, Email 
                                   FROM Contacts) 
                           FROM Account 
                           WHERE Id in (SELECT AccountId FROM Contact) AND
                           Contact.Email != null

The error message: Didn't understand relationship 'Contact' in field path.

I thought Contacts was a child element, so how do you access the field of a child element through the parent? Thank you so much for your help!
Rahul.MishraRahul.Mishra
Hi,

In this case , I would suggest to write query on conact:
  
SELECT Id, Name, AccountId, Account.Name, Email From Contact Where Email != null AND AccountId != null

In result, we will get all the account whose contact's email is not null. You can collect those account's id in set:
  
List<Contact> lstCon = [SELECT Id, Name, AccountId, Account.Name, Email From Contact Where Email != null AND AccountId != null];

Set<Id> setAccountId = new Set<Id>();
for(Contact con: lstCon) {
  setAccountId.add(con.AccountId);

}

Mark answer solved if it does help you.
Support 3743Support 3743
@Rahul.Mishra, sorry for bugging you so frequently. Is it possible to get infor about Opportunity when selecting Contact? Like this:
Select Opportunity.Stage FROM Contact
Do I need to create a custom lookup relationship for this? If so, how do I do it? Thanks!
Rahul.MishraRahul.Mishra
Hi,
There is no direct relationship between Contact and Opportunity, contact and opportunities are linked with each other through intermidiate object : OporrtunityContactRole, you can still be able to prepare the Map of Contact Id --> List<OpportunityId>, based on your requirments you can use opportunityId to query further opportunity:
  
List<OpportunityContactRole> lstOCR = [Select COntactId, OpportunityId From ContactRole];

Map<Id, list<Id>> mapOfConToOppIds = new Map<Id, list<Id>>();

 for(OpportunityContactRole OCR : lstOCR) {
   if(!mapOfConToOppIds.containsKey(OCR.ContactId) {

     List<Id> lstOppId = new List<Id>();
     lstOppId.add(OCR.OpportunityId);
     mapOfConToOppIds.put(OCR.ContactId, lstOppId);
 } else {
  mapOfConToOppIds.get(OCR.ContactId).add(OCR.OpportunityId);

  }
}