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

2 Answers 2


You can use OpportunityLineItem to traverse from Opportunity to PricebookEntry and Product..

something like this query

SELECT Id, Name, Account.Name, 
  (SELECT Quantity, UnitPrice, TotalPrice, 
   PricebookEntry.Name, PricebookEntry.Product2.Family FROM 
FROM Opportunity WHERE Id = :YourOppId

You can also refer this object model to understand the relationship between Product, Pricebook, OpportunityLineItem and Opportunity objects

  • 2
    i was only 1 minute off...sigh
    – cropredy
    Commented Apr 23, 2015 at 1:17
  • @VamsiKrishna Thanks, please how do I select all fields from product2 ?
    – JuhaMdev
    Commented Apr 23, 2015 at 16:15
  • there is no Select * .. so you have to include the fields you want in the query. you can check the product2 object for all the available fields and add them in. Commented Apr 24, 2015 at 0:42

Try this

select id, quantity, unitPrice, totalPrice, productCode, product2.someFldOnProduct2, 
       opportunity.name, opportunity.amount, opportunity.somefieldOnOppo
    from OpportunityLineItem 
    where opportunity.stageName = 'Closed'

You need to be at Version 30 or higher to do this as the lookup relationship from OpportunityLineItem to Product2 didn't exist until then.

Grouping / aggregating omitted from the above, exercise left to the reader; you could also do summarization/grouping in Apex

You must log in to answer this question.

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