I have the next objects:

Student__c, School__c, pire__c

pire__c has the student__c as a lookup field.

school__c has the student__c as a lookup field.

I having a SOQL query on pire__c and I need to get some data from student__c which is easy - But I also need to get data from the related school__c object.

What I have so far :

[SELECT Id,Name,Student__r.Name,Student__r.hed__Gender__c,Student__r.id_number__c FROM Pire__c]

What I need to accomplish:

[SELECT Id,Name,Student__r.Name,Student__r.hed__Gender__c,Student__r.id_number__c,(SELECT Id,Name FROM *Student__r.School_Student_2_school_child_relation *) FROM Pire__c]

How can I get the related School__c from the Student__c when I make a SOQL on the Pire__c object?

Is there any easy way like I wrote?


The reason I didn't turn Student to be the main query is that it can have more than 20k records while running on Pire__c will give 1-3k (exactly what I need). I cant do Semi-Join for example : [SELECT Id,Name FROM Student WHERE Id IN (SELECT Student__c FROM Pire__c) ] because I need to get Data from Pire__c also...


I have SOQL which go on pire__c object

  • What exact error do you get with the SOQL you tried to set up? Please add it to your question. – Phil W Nov 10 '19 at 10:10
  • Hey, I dont have any error message because I'm kinda stuck with what to write. I want to use the child relationship name of a parent object - which the parent is not the main running of the SOQL.. From my example - my issue is that the relationship needs to be on the student child and not on the Pire__c child... hope it more clear now,, – Salvation Nov 10 '19 at 13:27
  • Go up a level: `SELECT Id, (SELECT Pire stuff), (SELECT School stuff) FROM Student WHERE Id IN (SELECT StudentId FROM Pire WHERE Id = StudentId) – Caspar Harmer Nov 10 '19 at 17:23

It depends partially on how you need to use the data. It may be easier for you execute two queries, but you could also try something like the following to do it in a single query:

[SELECT Id, (SELECT Id FROM Pire__r), (SELECT Id FROM School__r) FROM Student WHERE Id IN (SELECT Student__c FROM Pire__c)]

This should return a list of Students and should contain any Pire and School information associated with that student.

  • Hi, thanks for your answer. I just updated the question- Please check it out under note section which I explained why this is not really a good idea in my case. Sorry that I didn't mention it before. – Salvation Nov 11 '19 at 6:14
  • @Salvation I edited my answer to include a WHERE clause that would only return students who have a Pire record associated with them. – Nicholas Savini Nov 11 '19 at 16:41
  • Hmm.. that really can be an option. Please let me check it. Thanks! – Salvation Nov 12 '19 at 8:07

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.