1

I'm trying to send some SOQL in the REST API, in order to JOIN all entries from Product2 with their UnitPrice value in the corresponding PricebookEntry in the standard price book.

Either a LEFT JOIN or INNER JOIN could possibly work for my scenario. – Here's my naive attempt:

SELECT p.Id, 
       p.Description, 
       p.IsActive, 
       p.Name, 
       p.ProductCode, 
       (SELECT Product2Id, 
               UnitPrice 
        FROM   PricebookEntry pe) 
FROM   Product2 p 
WHERE  pe.Product2Id =: p.Id 

The above query fails with:

'bind variables only allowed in Apex code'

What's the part that I'm missing? Is this really possible only in Apex?

4
  • 1
    You forgot the = : in the where clause before the p.Id .. I used to do that too ! So you want to add an extra : before p.Id Mar 9, 2017 at 9:05
  • +1 Thank you, Simon. That was a typo which I've now fixed. However I'm still getting the same error... Mar 9, 2017 at 9:07
  • @NikosBaxevanis: check my answer.
    – Patlatus
    Mar 9, 2017 at 9:07
  • Ah.. make sure to use the relationship API name for the table in the Subselect, so it should be FROM PricebookEntries Mar 9, 2017 at 9:14

1 Answer 1

2

There is no concept of INNER JOIN or OUTER JOIN. Instead, there are SEMI JOIN and ANTI JOIN in SOQL.

The query you are looking for is this one:

SELECT p.Id, 
       p.Description, 
       p.IsActive, 
       p.Name, 
       p.ProductCode, 
       (SELECT Product2Id, 
               UnitPrice 
        FROM   PricebookEntries) 
FROM   Product2 p
4
  • Hmm... However, when I execute this in Apex Explorer, I still see only the fields from the Product2 table. I don't see UnitPrice anywhere in the results. Mar 9, 2017 at 9:08
  • 1
    @NikosBaxevanis You need to use a tool that supports it. The Developer Console and the Data Loader both don't support it. Try using the Workbench.
    – sfdcfox
    Mar 9, 2017 at 9:15
  • But then, will I be able to use that query through the Salesforce REST API? Mar 9, 2017 at 9:16
  • 1
    @NikosBaxevanis Certainly. Try it out in the REST Explorer in the Workbench.
    – sfdcfox
    Mar 9, 2017 at 9:17

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.