Join 3 Salesforce objects using soql

Question

I am trying to join the Pricebook2, Product2 and PricebookEntry objects together but do not understand how the syntax will work.

The relationship between the 3 objects are Pricebook2 is 1 to many with PricebookEntry and Product2 is 1 to many with PricebookEntry. I need to join the 3 tables and filter on the Family field in Product2 and the Name field in Pricebook2.

This query works to join Pricebook2 to PricebookEntry:

SELECT Name,(SELECT Name FROM PricebookEntries) FROM Pricebook2

and this query works to join Product2 to PricebookEntry:

SELECT Name,(SELECT Name FROM PricebookEntries) FROM Product2

How can I take the SOQL above and join the 3 tables together in 1 query?

Thanks

Daniel Knowlton Daniel Knowlton · Accepted Answer · 2019-01-15T23:30:04

As you pointed out there's a one-to-many relationship between the products and the price book entries, and also between the price books and the price book entries. Another way to look at it is there's a many-to-one relationship between the price book entries and the products, and also the price book entries and the price books. In the SQL world, you would select from PriceBookEntry and INNER JOIN the Product2 and Pricebook2 tables to get the additional field information in addition to the foreign key fields (i.e. the Ids) that are in the PricebookEntry table. Like this:

SELECT
  pbe.IsActive, pbe.UnitPrice, pbe.UseStandardPrice, 
  pbe.Pricebook2Id, pb.Name, pb.Description, pb.IsActive, pb.IsStandard, 
  pbe.Product2Id, p.Name, p.Description, p.Family, p.ProductCode, p.IsActive
FROM PricebookEntry pbe
INNER JOIN Pricebook2 pb ON pbe.Pricebook2Id = pb.Id
INNER JOIN Product2 p ON pbe.Product2Id = p.Id

Simple right? Using this same logic, you just need to convert the SQL syntax to the Salesforce SOQL way, which is to use the "dot notation" instead of INNER JOINs. So the equivalent SOQL query looks like this:

SELECT 
  IsActive, UnitPrice, UseStandardPrice, 
  Pricebook2Id, Pricebook2.Name, Pricebook2.Description, Pricebook2.IsActive, Pricebook2.IsStandard, 
  Product2Id, Product2.Name, Product2.Description, Product2.Family, Product2.ProductCode, Product2.IsActive
FROM PricebookEntry

Join 3 Salesforce objects using soql

1 Answers