Add Recipe

  

Recipes by Category
App Distribution (13) Bundle logic, interface and services for distribution. App Logic (52) The Apex programming language, workflow and formulas for logic. Collaboration (7) The Salesforce Chatter collaboration platform. Database (29) Data persistence, reporting and analytics. Integration (57) Web Service APIs and toolkits for integration. Security (22) Platform, application and data security. Tools (17) Force.com tooling User Interface (38) Visualforce MVC and metadata-drive user interfaces. Web Sites (13) Public web sites and apps with optional user registration and login.
Beta Feedback
Cookbook Home » Using Relationship Queries
Verify this Recipe View source code

Using Relationship Queries

Post by Developer Force  (2010-07-16)

Status: Certified
Level: novice

Problem

You want to use as few SOQL queries as possible to access data from multiple related objects.

Solution

Use SOQL relationship syntax to pull data from related records in a single query.

For each of the following examples, the child object is the object on which the relationship field (the foreign key) is defined, and the parent is the object that the child references:

Basic Child-to-Parent (Foreign Key) Traversal

To traverse a relationship from a child to a parent, use standard dot notation off the name of the relationship. For example, this SOQL query retrieves information about contacts from the Contact object, along with the name of each contact's related account (the parent object): 
SELECT Id, LastName, FirstName, Account.Name
FROM Contact
Account is the name of the relationship that's defined by the AccountId lookup field on the Contact object. Using dot notation, this SOQL query retrieves the Name field on the account that is related through the Account relationship.

Expanded Child-to-Parent (Foreign Key) Traversal

Child-to-parent traversals can extend up to five levels from the original root object. For example, the last selected field in this SOQL statement extends two levels from the root contact record by retrieving the name of the parent account on the account associated with the contact: 
SELECT Id, LastName, FirstName, Account.Name, Account.Parent.Name FROM Contact

Basic Parent-to-Child (Aggregate) Traversal

To traverse a relationship from a parent to a set of children, use a nested query. For example, this SOQL query retrieves opportunities and the opportunity products associated with each opportunity: 
SELECT Id, Name, Amount, 
       (SELECT Quantity, UnitPrice, TotalPrice
        FROM OpportunityLineItems)
FROM Opportunity
Using the nested query, we're specifying that for each opportunity we want the respective set of OpportunityLineItem records that are related through the OpportunityLineItems child relationship.

Combined Child-to-Parent and Parent-to-Child Traversal

Foreign key and aggregate traversals can also be combined in a single query. For example: 
SELECT Id, Name, Account.Name, 
       (SELECT Quantity, UnitPrice, TotalPrice, 
               PricebookEntry.Name, 
               PricebookEntry.Product2.Family
        FROM OpportunityLineItems)
FROM Opportunity

Share

Recipe Activity - Please Log in to write a comment

Its a nice article. But here, there is no example where there is a child-Parent relationship on a single table.

For example, we have table "Contact". There are fields Id, Name and Reports To fields.

The "Reports To" field is again lookup to Contact.

Can you please explain how to define this relationship using SOQL?

Best Regards
Gowtham

by Gowtham Sen Potnuru  (2012-02-08)

 Sorry about the problem in the example all - it's fixed now.

by Jon Mountjoy  (2011-01-19)

 For "Basic Parent-to-Child (Aggregate) Traversal", they probably meant something like:
p.p1 {margin: 0.0px 0.0px 0.0px 0.0px; font: 11.0px Monaco} span.s1 {color: #961a65} span.s2 {color: #2f41e2}

SELECT Id, AccountId, Name, (SELECT Quantity, TotalPrice from OpportunityLineItems) from Opportunity


by cirro  (2010-12-31)

Nice ... the examples for "Basic Parent-to-Child (Aggregate) Traversal" and "Combined Child-to-Parent and Parent-to-Child Traversal" are botched.  They are the same as the previous two examples and do not even contain the same Object Name offered in the text.

by sweeper  (2010-12-27)

 yeah, it's pretty bad when the query is just copy/pasted multiple times...and worse that this is already in the documentation, and probably a botched copy/paste from that...

by paul-lmi  (2010-11-03)

This would be helpful but at the moment the SELECT statement syntax is exactly the same on each example. I think it needs to be reviewed!

by a0930000006io9Y  (2010-10-12)

X

Vote to Verify a Recipe

Verifying a recipe is a way to give feedback to others and broaden your own understanding of the capabilities on Force.com. When you verify a recipe, please make sure the code runs, and the functionality solves the articulated problem as expected.

Please make sure:
  • All the necessary pieces are mentioned
  • You have tested the recipe in practice
  • Have sent any suggestions for improvements to the author

Please Log in to verify a recipe

You have voted to verify this recipe.