Apex Code Development You need to sign in to do that
Don't have an account?
RD7408 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
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
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
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.