+ Start a Discussion
chad tetreault 1chad tetreault 1 

Relationship Queries

Hi everybody, I've been having a tricky time getting this query to work. I'm trying to get all the data from Contact, and also, the AccountName that the Contact is ascociated with.

This query works great, but doesn't give me the AccountName of course. I'm hoping somebody could shed some light on how to add in the AccountName portion via a relationship query.

Here's my current, working query:

SELECT Contact.Name, Contact.FirstName, Contact.LastName, Contact.Id, Contact.Phone,Contact.Title, Contact.Department, Contact.PhotoUrl, Contact.Email, Contact.MailingCity, Contact.MailingState, Contact.MailingCountry, Contact.MailingPostalCode, Contact.MailingStreet LIMIT 100

Thank you
 
JAY_PJAY_P
SELECTcontact_r.accountname, Contact.Name, Contact.FirstName, Contact.LastName, Contact.Id, Contact.Phone,Contact.Title, Contact.Department, Contact.PhotoUrl, Contact.Email, Contact.MailingCity, Contact.MailingState, Contact.MailingCountry, Contact.MailingPostalCode, Contact.MailingStreet LIMIT 100
try this one and let me know 
chad tetreault 1chad tetreault 1
Update.

I'm able to return a list of accounts which contains their contacts like this:

var query = "SELECT Name, (SELECT LastName FROM Contacts) FROM Account";

However, I'm unable to return a list of Contacts which contain the Account Name like this:

var query = "SELECT Name, (SELECT Name FROM Accounts) FROM Contact";
 
chad tetreault 1chad tetreault 1
Thanks Jay_P. Didn't work ;)

Here's the error message: " Contact.MailingPostalCode, Contact.MailingStreet LIMIT 100 ^ ERROR at Row:1:Column:291 unexpected token: LIMIT"
JAY_PJAY_P
Remove limit 100 from your query and try it one more time 
chad tetreault 1chad tetreault 1
This time  (without the LIMIT)

" Contact.MailingPostalCode, Contact.MailingStreet ^ ERROR at Row:1:Column:291 unexpected token: '<EOF>'"

Query:

SELECT contact_r.accountname, Contact.Name, Contact.FirstName, Contact.LastName, Contact.Id, Contact.Phone, Contact.Title, Contact.Department, Contact.PhotoUrl, Contact.Email, Contact.MailingCity, Contact.MailingState, Contact.MailingCountry, Contact.MailingPostalCode, Contact.MailingStreet
JAY_PJAY_P

SELECT contact_r.accountname,Name, FirstName, LastName, Id, Phone, Title, Department, PhotoUrl, Email, MailingCity,MailingState, MailingCountry, MailingPostalCode, MailingStreet from contract limit 100 
and make sure you are using correct API name for the field ...
Thank You
chad tetreault 1chad tetreault 1
Different error this time with the following query, changed "contract" to "Contact" as well:

SELECT contact_r.accountname, Name, FirstName, LastName, Id, Phone, Title, Department, PhotoUrl, Email, MailingCity,MailingState, MailingCountry, MailingPostalCode, MailingStreet FROM Contact LIMIT 100

Error:

" SELECT contact__r.accountname, Name, FirstName ^ ERROR at Row:1:Column:8 Didn't understand relationship 'contact__r' in field path. If you are attempting to use a custom relationship, be sure to append the '__r' after the custom relationship name. Please reference your WSDL or the describe call for the appropriate names."
 
Rajneesh Ranjan 23Rajneesh Ranjan 23
Hi JAY_P,

Greetings!!!

Before you run the SOQL, you have to understand the relationship between objects. Based on the relationship there could be two types of SOQL.
  1. Child to Parent (i.e. you are trying to fetch parent object data while querying on Child object)
  2. Parent to Child (i.e. you are trying to fetch child object data while querying on Parent object)
Now, If we talk about Child to Parent , Below is the syntex:
Example:1
SELECT Account.Name, Name, FirstName, LastName, Phone, Department, Email from Contact where Phone <> null limit 10
Note: Here Account is Parent of Contact and it is standard object, hence we have to write it as Account.Name (Please see my next example when Custom object is Parent of another object)

SOQL - Child to Parent - Standard Object
Example2:
SELECT Course__r.Name , Name from Student__c
Note: Here Course is the Parent of Student, and these are custom objects, hence we have to write it as Course__r, that represents relationship.
Child_to_Parent_Custom

Now, If we talk about Parent to Child relationship, then SOQL would be as below:
Example1:
SELECT Account.Name,(SELECT Contact.Name FROM contacts) FROM Account
Note: Here Account acts as a parent object and both are Standard object hence we are not representing relatonship with __r here. (Please see example with custom object below)

User-added image

Example2:
Select Employee_Name__c, (Select Qualification__c, Passing_Year__c, Percentage__c from Education_Detail__r) from Employee__c
Note: Here both Parent and Child object are custom object. Employee__C is parent here. Hence we have to represent relationship with __r while fetching child related record.

User-added image

Hope this will help you..!!!

Thanks,
Rajneesh
Rajneesh Ranjan 23Rajneesh Ranjan 23
Hi Chad,

Hope you are good with this concept/query now. Could you please spare few moments and mark one answer as best answer here.

Thanks,
Rajneesh