function readOnly(count){ }
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
+ Start a Discussion
jackeejackee 

Parent-Child query and Child Parent query in same query

link to the ERD   - ERD

 

I am able to get record from Contact to Donation below is the working query 

 

SELECT ID,Name,Past_Donation_Total__c,(SELECT Opportunity.amount FROM OpportunityContactRoles Order BY Opportunity.CloseDate DESC Limit 1 ) FROM Contact Where Lead_Source_Contact__c = \''+searchText+'\' ';

 

In the same query I would like to get few fields from Household too. Is that possible ? I got error message saying you can go up to 1 level only.

 

Something like below is red, but i m getting error message;

 

SELECT ID,Name,( Select Name from Household__C),Past_Donation_Total__c,(SELECT Opportunity.amount FROM OpportunityContactRoles Order BY Opportunity.CloseDate DESC Limit 1 ) FROM Contact Where Lead_Source_Contact__c = \''+searchText+'\' ';

 

TIA!!

Best Answer chosen by Admin (Salesforce Developers) 
Edwin VijayEdwin Vijay

If Household__c is a lookup on Contact, then Household__r.Name from the Contact should work i believe

All Answers

Edwin VijayEdwin Vijay

Basically you will need to identify the relationship between the objects.. below is a sample of a nested query

 

List<Account> accounts = [Select Id,(Select TeamMemberRole, User.Name From Account.AccountTeamMembers), Name, BillingCountry from Account];

 

Shashikant SharmaShashikant Sharma

Could you tell the relationship between Household__C and Contact . And the error message is conveying that you can only get to one level to your child in a child query that means you can not get child of child.

Edwin VijayEdwin Vijay



SELECT ID,Name,Household__r.Name,Past_Donation_Total__c,(SELECT Opportunity.amount FROM OpportunityContactRoles Order BY Opportunity.CloseDate DESC Limit 1 ) FROM Contact Where Lead_Source_Contact__c = \''+searchText+'\' ';

 

I guess you can have the query like this

jackeejackee

This is non-profit instance. 

 

In contact household__c is a lookup field in this case, which means from household__c i could query contact fields( which I am using contact__r) but not from contact  to household i m not able to field of household.... is that right ?  (for more details look the ERD attached to the orginal message)

 

ERD link : http://wiki.developerforce.com/index.php/File:EntityDiagram.jpg

 

If we can not get the household field from contact ... based on the object relarionship setup ..... How can I achieve this ? 

 

1. Use a for loop to get the household field for that record in a new list and then display them using table row...

2. Write a trigger to update household with the contact relationship ... and then  write the query.....

3......

 

TIA!!

 

 

 

 

 

 

 

Edwin VijayEdwin Vijay

If Household__c is a lookup on Contact, then Household__r.Name from the Contact should work i believe

This was selected as the best answer
jackeejackee

select ONEN_Household__r.id,ONEN_Household__r.name         from  contact

 

 

Thanks you al it worked.