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?

Improve this question
2
  • 1
    How do you link Opportunity and Contact, since there is no standard lookup between the two objects?
    – DavidSchach
    Commented Apr 17, 2014 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
    Commented Apr 17, 2014 at 22:38

2 Answers 2

Reset to default
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')
Improve this answer
2
  • 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
    Commented Apr 17, 2014 at 21:40
  • 1
    My fault for moving too fast, I changed the WHERE clause to look at AccountId instead :)
    – Nathan Williams
    Commented Apr 17, 2014 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')]
Improve this answer
4
  • 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
    Commented Apr 17, 2014 at 22:27
  • 2
    Good catch! Updating...
    – DavidSchach
    Commented Apr 17, 2014 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
    Commented Apr 17, 2014 at 22:36
  • Thanks! Both work great. Wish I could take em all as best answer.
    – Joshua Dance
    Commented Apr 17, 2014 at 23:00

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .