Retrieving Records from Multiple Objects using the Apex Data Loader

In the Salesforce.com Advanced Administration (ADM-301) class, we have an exercise using the Apex Data Loader that has the student extract Account records meeting a certain criteria. There is a follow-up question to the exercise that asks:

Question:
Is it possible, using the Data Loader, to extract only Accounts that have Opportunities that have Products associated with them?

Answer:
Yes. Although the Data Loader appears to only be able to retrieve data from a single object, it can actually retrieve data from multiple objects. The trick is to query the lowest level object and include the parent records through the child to parent relationship. To achieve our goal, we need to query the OpportunityLineItem table, which is where the products related to Opportunities are stored. We know that these records must be associated to an Opportunity and we know that Opportunities are normally associated with Accounts. Therefore, query the OpportunityLineItem table will only give us Accounts that have Opportunities with Products.

The SOQL statement to achieve this is:

Select Id, OpportunityId, Opportunity.Account.Id, Opportunity.Account.Name, Opportunity.Name, PricebookEntry.Product2.Name FROM OpportunityLineItem

Simply run an extract using the data loader and paste this statement into it to see the results.