I want to query products only related to particular opportunity. And I tried this using sub-queries but I was able to retrieve PricebookEntries related to Opportunities. I'll hit a dead end if I tried joining the results with Product2.
How to go about this?
SELECT Id from Product2 where Id in (
SELECT Product2Id
FROM PricebookEntry
WHERE Pricebook2Id in (Select Pricebook2Id FROM Opportunity WHERE StageName = 'Closed')
)
You do a sub-select, e.g:
Select Amount,CloseDate,Name, (Select PricebookEntry.Product2Id, TotalPrice, UnitPrice, ListPrice From OpportunityLineItems) From Opportunity WHERE StageName = 'Closed' ORDER BY Amount DESC NULLS LAST
Opportunity Products (OpportunityLineItem) is a Detail to the Master Opportunity, i.e. 1:n between Opportunity and Product lines. Products belong to a Pricebook, thus you have to traverse the PricebookEntry relationship to retrieve the Product ID (if you need it).
You can also query having OpportunityLineItem as the Primary object and referencing Opportuinty via the Opportunity relationship:
Select Select PricebookEntry.Product2Id, TotalPrice, Opportunity.Amount, Opportunity.CloseDate, Opportunity.Name From OpportunityLineItem WHERE Opportunity.StageName = 'Closed' ORDER BY Opportunity.Amount
You can't go directly from Product2 to Opportunity because there is no direct relationship between the two, i.e. the relationship is via PricebookEntry.
A useful tool in discovering relationships between objects is to use the Force.com Eclipse plugin. You can double click on the salesforce.schema item and it will open a GUI for building queries:
Your main query (ignoring the nested select) is essentially "Select Name, Id From Product2" so you get all products (subject to query limitations). Adding in the nested select says "for every row returned, also get the related PricebookEntries (but only from the given Pricebook)". So you still get all products + some extra related info if there's an entry in that Pricebook.
Everything you need is in PricebookEntry:
Select Id, Name, ProductCode, Product2Id, UnitPrice
From PricebookEntry where Pricebook2Id = '<insert id>'
Best Answer
You can use OpportunityLineItem to traverse from Opportunity to PricebookEntry and Product..
something like this query
You can also refer this object model to understand the relationship between Product, Pricebook, OpportunityLineItem and Opportunity objects