the inner select field cannot have more than one relationship in SOQL

Question

I have a relationship like below and i get an error like - the inner select field Customer_r cannot have more than one relationship.

I want to find customers where products are having customer id and belongs to the books category.

Customers_c

• id
• name
• Country
  

Products

• id,
  
• name
• category
• Customer_r --> id, name

I tried with the below query but not worked.

SELECT id, name from Customers_c WHERE id IN (SELECT Customer_r.id FROM Products WHERE Customer_r .id != NULL AND category = 'BOOKS')

Answer 1

Instead of using __r.Id, use __c instead.

SELECT Name FROM Customers__c WHERE Id IN (SELECT Customer__c FROM Products WHERE Category = 'Books')

There's no need to filter out null values, as this happens automatically when filtering on Id.

In general, you should use __c when you want the Id from a lookup field.