0

I am trying to generate a query in APEX related to the Opportunity and Contact objects. I want to select Opportunities based on the info contained in the FirstName field of the linked Contact.

In SQL I would join the tables and then select but in SOQL we use sub-queries. I tried to pattern mine off the answer to this question which successfully used sub-queries but I don't yet get the 'SOQL' way.

My most current failing query is:

SELECT 
    Name, 
    (SELECT 
        FirstName
    FROM 
        Contact)
FROM 
    Opportunity
WHERE 
    FirstName IN (SELECT FirstName FROM Contact WHERE FirstName = 'Jane') 

Any ideas?

  • 1
    How do you link Opportunity and Contact, since there is no standard lookup between the two objects? – DavidSchach Apr 17 '14 at 22:08
  • @DavidSchach Didn't know there wasn't a standard lookup. Also didn't realize it was the AccountId. I'm a rookie at Salesforce dev still. Thanks. – Joshua Dance Apr 17 '14 at 22:38
2

The idea here is that you are first selecting all Contacts with FirstName of Jane here SELECT OpportunityId from Contact WHERE FirstName = 'Jane' and pulling the OpportunityId for each of those records... You then feed that into a typical Opportunity query where the Opportunity.AccountId = the Contact.AccountId list you just created with the sub-query above...

SELECT
    Id
FROM
    Opportunity
WHERE
    AccountId IN (SELECT AccountId from Contact WHERE FirstName = 'Jane')
  • Makes sense but OpportunityId is not a column on Contact. 'INVALID_FIELD: SELECT OpportunityId from Contact WHERE ^ ERROR at Row:1:Column:8 No such column 'OpportunityId' on entity 'Contact'. If you are attempting to use a custom field, be sure to append the '__c' after the custom field name. Please reference your WSDL or the describe call for the appropriate names.' Do I need to make a custom field? – Joshua Dance Apr 17 '14 at 21:40
  • 1
    My fault for moving too fast, I changed the WHERE clause to look at AccountId instead :) – Nathan Williams Apr 17 '14 at 21:45
2

A few points:

  1. Subqueries can only pull Id fields (such as Id, AccountId, etc)
  2. Contact and Opportunity do not relate, but if you have a field on Opportunity called Contact__c, then the related list on Contact is probably called Opportunities__r. A field on Contact called Opportunity__c would have a related list called Contacts__r.
  3. Given your comment on @Nathan Williams' answer, have you not created any custom fields? If so, you will need to relate the two objects.

If you did put a Contact__c field on Opportunity, then the query could be (using a fuzzy match instead of an exact match):

[SELECT Id FROM Opportunity WHERE Contact__c IN (SELECT Id FROM Contact WHERE FirstName LIKE 'Jane%')]

If you put an Opportunity__c field on Contact (giving a reciprocal Contacts__r related list), then the query would be:

[SELECT Id FROM Opportunity WHERE Id IN (SELECT Opportunity__c FROM Contacts__r WHERE FirstName = 'Jane')]
  • 1
    ... your second query would actually bring back every Opp, but each opp would only show the contacts where the first name = Jane... not sure that's exactly what he'd want... your second example should be SELECT Id FROM Opportunity WHERE Id IN (SELECT Opportunity__c FROM Contacts__r WHERE FirstName = 'Jane') right? – Nathan Williams Apr 17 '14 at 22:27
  • 2
    Good catch! Updating... – DavidSchach Apr 17 '14 at 22:32
  • 1
    Josh, either David's approach or mine will work just fine. I've tested mine out in dev console and it works fine, but you won't be able to get a list of the contacts as part of your query (you'll only be able to do that in @david's queries since they are directly related)... You've got three great options here! – Nathan Williams Apr 17 '14 at 22:36
  • Thanks! Both work great. Wish I could take em all as best answer. – Joshua Dance Apr 17 '14 at 23:00

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.