Relationship queries (parent-child)

Question

I am new to Salesforce and I am struggling to get my head around the correct SOQL which correctly reference multiple tables.

Some background:

I have a custom field in Order Product called Teacher_c. This field is related to Contact with child relationship name Order_Products2.

In the WSDL - For Contact:

<element name="Order_Products2__r" nillable="true" minOccurs="0" type="tns:QueryResult"/>

In the WSDL - For OrderItem:

<element name="Teacher__c" nillable="true" minOccurs="0" type="tns:ID"/>
<element name="Teacher__r" nillable="true" minOccurs="0" type="ens:Contact"/>

I have tried to write SOQL to get all order items and include the teacher name such as:

SELECT OrderItem.Id, OrderItem.UnitPrice, OrderItem.Quantity, Teacher_r.name
    FROM OrderItem

I know the error is Teacher_r.name but I have tried a number of different attempts without luck.

Any ideas?

Answer 1

You should be able to reference the Teacher table through the relationship using the denotion "Teacher__r". I am not sure if this is just a copy and paste error but the relationship that you pasted into your code only has 1 underscore and looked like "Teacher_r" while it should look like "Teacher__r". Also in the error message that you mention in the comment to @Nick Cook's answer:

When I try Teacher_r.name,Contact.Teacher_r.name it get the following error: "Didn't understand relationship 'Contact' in field path. If you are attempting to use a custom relationship, be sure to append the '__r' after the custom relationship name."

When choosing fields to be returned in the SELECT portion of the SOQL query you do not need to state the name of the object (data table) the fields (columns) live on, you can simply reference them as follows:

list<OrderItem> orderItems = [SELECT Id, UnitPrice, Quantity, Teacher__r.Name FROM OrderItem];

The above query will bring back a list of order item records with the fields Id, UnitPrice, Quanitity and Teacher__r.Name.

Answer 2

It's hard to tell because you're using the terms 'Order' and 'Product' interchangeably. By the looks of it, you're doing a query on the OrderItem object, but the Contact lookup is on the Order object.
This means you have three levels of relationship (a grandparent).

try:

SELECT Id, UnitPrice, Quantity, Order.Teacher__r.Name FROM OrderItem