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?

Attribution to: Joshua Dance

Possible Suggestion/Solution #1

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')

Attribution to: Nathan Williams

Possible Suggestion/Solution #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')]

Attribution to: DavidSchach
This content is remixed from stackoverflow or stackexchange. Please visit https://salesforce.stackexchange.com/questions/33073