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.
SELECT id,Pet_Name__c FROM Pet_Details__c WHERE NoOfAdoptions__c > 0
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
SELECT id,Pet_Name__c,(SELECT id,.User_Id__c FROM Adoptions__r) FROM Pet_Details__c WHERE NoOfAdoptions__c > 0
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>
- Listname.get(0).id -> the ID of the Pet_Details__c record
- Listname.get(0).Pet_Name__c -> the Pet_Name__c of the Pet_Details__c record
- Listname.get(0).Adoptions__r -> gives you a List that are the children of the particular Pet_Details__c record. In this list as of now 2 fields are present - ID and User_Id__c
Now lets expand the sub query and query out the parent user values too.
SELECT id,Pet_Name__c,(SELECT id,.User_Id__c,User_Id__r.UserName,User_Id__r.Email,User_Id__r.Phone FROM Adoptions__r) FROM Pet_Details__c WHERE NoOfAdoptions__c > 0
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