function readOnly(count){ }
Sign Up ›
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
Learn More

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
Aron SchorAron Schor 

SOQL query showing Accounts that only purchase products belonging to select Product Families

I need help writing a SOQL query to see Accounts that purchase only products associated with Product Family "Clauss" and/or "Camillus."

I am guessing I need to do a special query that utilizes roll ups which is a bit over my head.  Below are some details I assume would be useful in terms of the relationships between fields.

I can see the Account info from Order_Header__c
SELECT Account__C, Customer_Number__c FROM Order_Header__c LIMIT 10

If I go to Order_Header__C and Child Relatonships I see
Order_Detail__c.Order_Header__c

I can see Product Family if I do this query.
SELECT Family FROM Product2 LIMIT 10

If I go to Product2 for Child Relationships it says
Order_Detail__c.Product__c

I can see the base stock code here
SELECT Product__c, Base_Stock_Code__C FROM Order_Detail__c LIMIT 10

Thanks, Aron.
KevinPKevinP
Aron, 

You've not really given me enough to go on for a complete query, but I can start you donw the right path. 

Given a query where you want account data for accounts with orders containing items from a given product family, you can structure your query with a complex where clause. Imagine something like this:
  
SELECT Id, Name, whatever FROM Account WHERE ID in (SELECT Account__c FROM Order_Detail__c WHERE Order_Detail__c.product2.Family == 'family name')

Obviously that's not ready to run but... should get you started.
Aron SchorAron Schor
Kevin, I tried a a few things but couldn't get anything to work.  I figured this would be tough to do, but thanks for replying and offering your assitance.