Find all Product2 records that do NOT have a PricebookEntry in the Standard Price Book

Question

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.

Answer 1

The Id column in your subquery is the Id of the PricebookEntry object. You want the reference field to Product2, Product2Id:

SELECT Id, ProductCode 
FROM Product2 
WHERE Id NOT IN (SELECT Product2Id 
                 FROM PricebookEntry 
                 WHERE Pricebook2Id = '01si0000003vpAlAAI') 

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.