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:
FirstName IN (SELECT FirstName FROM Contact WHERE FirstName = 'Jane')
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...
AccountId IN (SELECT AccountId from Contact WHERE FirstName = 'Jane')
Subqueries can only pull Id fields (such as Id, AccountId, etc)
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.
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')]