+ Start a Discussion
RD7408RD7408 

Basic SOQL - Relationships instead of Joins:?

I know my question is going to be pretty simple for you veterans but can someone explain to me how relationships work in SOQL. I have looked at the documentation and the discussion boards but can not seem to understand it.

 

I want to get the following fields from the following tables:

 

Contact.FirstName,

Contact.LastName,

Account.Name,

Opportunity.Name

 

I have tried the following but it does not seem to work:

 

SELECT Contact__c.FirstName, Contact__c.LastName, Contact__c.Account__r.Name, Contact__c.Account__r.Opportunity__c.Name From Contact

 

The more I read the more confused I get.

 

I need to understand the basic concept. If someone could help I would appreciate it.

 

Robert

Best Answer chosen by Admin (Salesforce Developers) 
Ken KoellnerKen Koellner

You could do a sub-query to get children, as in the second query of the previous poster, or you can query parents.  But, the relationships have to be defined in SF, no arbitrary joins like in an RDBMS.

 

THere's a build-in relationship between Opp and Account.  So you could do.

 

  Select name, account.name from Opportunity.

 

That will get your Opportunity name and the Name of the account on the opportunity.


There isn't a native relationsip between Opportunity and Contact.  There is Contact Roles but that act is a built-in many-to-many.  There are some features in SF that are kinda magic, they do specific things and trying to treat them like generalities doesn't work. Contact Roles is one of those features.

 

Now, you coudl have a custom relatationship between Opp and Contact.  Let's say you had a custom field in Opp called Contact__c that was a lookup relationshpi to Contact.  Then you could code--

 

  Select name, account.name, contact_r.name from Opportunity.

 



That would work just fine.

 

The bottom line is simple left-outer joins work from child to parent if the relationship is defined in SalesForce.

 

 

All Answers

ptepperptepper

Hi Robert, 

 

You can't perform arbitrary joins, so I don't think you can get all that in one query.

 

You could do something like this to get all the contacts with their parent Accounts

 

SELECT FirstName, LastName, Account.Name FROM Contact

 

or this to get all the Accounts with their child contacts

 

SELECT Id, Name,  (SELECT Id, FirstName, LastName    FROM Contacts) FROM Account

 

Also I'm guessing the __c suffix is a mistake in there, because that's only for custom fields.

 

If you don't have it already, I'd recommend downloading the Force.com Explorer, it gives you a GUI to see most fields and relationships for the objects in your instance, builds queries automatically, and lets you test queries:

http://wiki.developerforce.com/index.php/ForceExplorer

 

 

Ken KoellnerKen Koellner

You could do a sub-query to get children, as in the second query of the previous poster, or you can query parents.  But, the relationships have to be defined in SF, no arbitrary joins like in an RDBMS.

 

THere's a build-in relationship between Opp and Account.  So you could do.

 

  Select name, account.name from Opportunity.

 

That will get your Opportunity name and the Name of the account on the opportunity.


There isn't a native relationsip between Opportunity and Contact.  There is Contact Roles but that act is a built-in many-to-many.  There are some features in SF that are kinda magic, they do specific things and trying to treat them like generalities doesn't work. Contact Roles is one of those features.

 

Now, you coudl have a custom relatationship between Opp and Contact.  Let's say you had a custom field in Opp called Contact__c that was a lookup relationshpi to Contact.  Then you could code--

 

  Select name, account.name, contact_r.name from Opportunity.

 



That would work just fine.

 

The bottom line is simple left-outer joins work from child to parent if the relationship is defined in SalesForce.

 

 

This was selected as the best answer