1

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?

Note:

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...

Thanks.

I have SOQL which go on pire__c object

Improve this question
3
  • What exact error do you get with the SOQL you tried to set up? Please add it to your question.
    – Phil W
    Commented Nov 10, 2019 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
    Commented Nov 10, 2019 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
    Commented Nov 10, 2019 at 17:23

1 Answer 1

Reset to default
1

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.

Improve this answer
3
  • 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
    Commented Nov 11, 2019 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
    Commented Nov 11, 2019 at 16:41
  • Hmm.. that really can be an option. Please let me check it. Thanks!
    – Salvation
    Commented Nov 12, 2019 at 8:07

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .