We have come to find that we have a small amount of products that have Product2 records, but no PricebookEntry for them. I'm trying to query for just those products, but I am having trouble with the relationships between them.
I have tried:
SELECT Id, ProductCode from Product2 WHERE id not IN (SELECT Id from PricebookEntry WHERE Pricebook2 = '01si0000003vpAlAAI')
However, that query won't run because of this error: The selected field 'Id' in the subquery and the left operand field in the where expression in the outer query 'id' should point to the same object type.
The hard coded id is our Standard Price Book. We only need to get this info once so I am not worried about hard coding that id.
Best Answer
The
Id
column in your subquery is theId
of thePricebookEntry
object. You want the reference field toProduct2
,Product2Id
:My first stop with this kind of issue is always the field reference for
PricebookEntry
(or whichever sObject is in question). It's a great resource for not just names but specific requirements and limitations around each standard field.