I think maybe someone asked the same type of question already but still i am not able to find the exact answer I am looking for.
I am having 3 custom objects namely
- User__c
- Pet_Details__c
- Adoption__c.
The adoption table has 2 master-detail lookup relationship fields and 1 unique adoption id field and 1 date field.
(These two below fields are like child-parent relationship type)
1.Pet_Id__c (Master table is Pet_Details__c)
2.User_Id__c (Master table is User__c)
Now I need to use a select query something like this,
Select Pet_Name__c(from Pet_Details__c table),
(Select UserName,Phone,Email(All these from User__c table)
FROM Adoption__c where Adoption_Date__c != null
I need this in above format so that I can display a parent-child grid like (For each pet how many users have have requested for adoption). Any help would be appreciated.
Best Answer
If your objective is finding how many users have requested for adoption of a pet, you could query from the Parent object(Pet) to the child(Adoption) using a sub query and in the sub query you can refer to the User parent's fields and fetch those info.
Lets assume that the child relationship name of Adoption is Adoptions__r. This can be found from the setting page of the field 'Pet_Id__c'. Lets assume that there exists a rollup summary field named NoOfAdoptions__c in Pet_Details__c object that counts the number of child adoption records present under it - https://help.salesforce.com/apex/HTViewHelpDoc?id=fields_defining_summary_fields.htm Lets assume that you are interested in the Pet_Name__c field(column) only in the Pet table(object) Lets assume that you are interested in UserName, Phone and Email fields on the User table(object)
So query from the Pets object, FILTERING out only those Pet_Details__c records which have adoption records under them, or order etc are left to your discretion.
Now lets get all the child adoption records associated with each of these Pet_Details__c records. For this we use a sub query - NOTE that I used the child relationship name for adoption
Now this query returns you a list of Pet_Details__c i.e List. Now each member of the list is a Pet_Details__c record and it gives the following 3 info:(lets access the first rec in the list>
Now lets expand the sub query and query out the parent user values too.
Now the query is complete. The query returns a List and each element of this list contains another List, which consists of the child adoption records under that pet detail record. And this list contains user info for each of the adoption record too.
Hope this helps