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 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
    Apr 23 '15 at 1:17
  • @VamsiKrishna Thanks, please how do I select all fields from product2 ?
    – JuhaMdev
    Apr 23 '15 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. Apr 24 '15 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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.