Search code examples
salesforceleft-joinsoql

LEFT OUTER JOIN for SalesForce don't work

I tried to execute left outer join to SalesForce Db to get Id and ProductCode fields from Product2 table and some fields from associated (if exists) PriceBookEntry. I have PriceBook2 with id='01sd00000008iWpAAI' too. Here is the sample query:

SELECT Id, ProductCode, 
(SELECT Id, PriceBook2Id, PriceBookEntry__r.Product2Id FROM PriceBookEntry__r 
WHERE PriceBook2Id='01sd00000008iWpAAI') 
FROM Product2 WHERE ProductCode IN 
('151','250','256','270','289')

The error returned to me is:

INVALID_TYPE: PriceBookEntry__r.Product2Id FROM PriceBookEntry 
WHERE PriceBook2Id='01sd00000008iWpAAI') 
^ ERROR at Row:1:Column:89 
Didn't understand relationship 'PriceBookEntry__r' in FROM part of query call. 
If you are attempting to use a custom relationship, 
be sure to append the '__r' after the custom relationship name. 
Please reference your WSDL or the describe call for the appropriate names..

I tried several variants for this query, but with no success. I have rows for these ProductCodes in Product2 table.

What i miss there?

Solution

  • I found the solution: The key is in the table PriceBookEntry. it should be in plural (PriceBookEntries). So the query should be:

    SELECT Id, ProductCode, 
(SELECT Id, PriceBook2Id, Product2Id FROM PriceBookEntries
WHERE PriceBook2Id='01sd00000008iWpAAI') 
FROM Product2 WHERE ProductCode IN 
('151','250','256','270','289')