Apex Code Development You need to sign in to do that
Don't have an account?
B2000 Relationship Query-Child Records from Parent Id Set Where Parent Id in a Set of Child Ids
I am trying to get a list of child records thru a single relationship query. I can do it with multiple queries, but want to know if I can do it with a single query. I want all child records from a set of parent ids where the parent id in a set of child Ids. Here is the query I tried but get an error in Eclipse "Malformed Query" row 1, col 116; unexpected token: ,
Select f.Family__c, f.Family_Role__c, f.Family_Member__c From Family_Member__c f where f.Family__c in (Select fam.Id, (Select fm.Family__c From Family_Members__r where fm.Family__c in :setOfFamily_Member__cIds) From Family__c fam)
Perhaps something like this...
List<Family__c> families = [ select Id, ( select Family__c, Family_Member__c From Family_Members__r ) from Family__c where Id IN ( select Family__c from Family__Member__c where Id IN :setOfAllMembers ) ];
Then, you'd have to loop through them like this to get your family members.
List<Family_Member__c> members = new List<Family_Member__c>();
for( Family__c f : families ) {
for( Family_Member__c fm : families.Family_Members__r ) {
members.add( fm );
}
}
All Answers
Assuming a master detail relationship, query via the master:
Family__c[] fam = [Select Family__c, Family_Role__c, Family_Member__c, (Select fm.Family__c From Family_Members__r) From Family__c Where ID IN :setOfFamily_Member_cIds];
Then to loop through the children:
For(Family__c f : fam){
for(Family_Member__c fm : fam.Family_Members__r){
//do stuff with the child records
}
}
It is a master detail relationship. My query would be slightly changed as the master and detail are reveresed.
Family__c[] fam = [Select f.Id, (Select Family_Member__c, Family__c, Family_Role__c From Family_Members__r where Family_Member__c in :setOfFamily_Member_cIds) From Family__c f];
However, won't this return all family records but with only family members that are in the set. I would like all the family member records, not the family records, in each family who has a family member in the set.
Family 1=>Member 1, Member 2, Member 3
Family 2=>Member 1, Member 4, Member 5
Family Member Set contains Member 1. I would like all 6 Family Member records returned as Member 1 belongs to Family 1 & 2.
Thanks for your quick reply!
You might try something like this:
List<Family_Member__c> someFamilyMembers; // this list contains some Family Members from different Families.
List<Family_Member__c> allFamilyMembers = [ select Family_Member__c
,Family__c,
,Family_Role__c
from Family_Member__c
where Family__c IN ( select Family__c from Family_Member__c where Id IN :someFamilyMembers ) ];
That was very close. I used:
Select f.Family__c, f.Family_Member__c From Family_Member__c f where f.Family__c in (Select fm.Family__c From Family_Member__c fm where Family_Member__c in :setofallmembers)
but got an error message"The inner and outer selects should not be on the same object type"
Perhaps something like this...
List<Family__c> families = [ select Id, ( select Family__c, Family_Member__c From Family_Members__r ) from Family__c where Id IN ( select Family__c from Family__Member__c where Id IN :setOfAllMembers ) ];
Then, you'd have to loop through them like this to get your family members.
List<Family_Member__c> members = new List<Family_Member__c>();
for( Family__c f : families ) {
for( Family_Member__c fm : families.Family_Members__r ) {
members.add( fm );
}
}
That did it thank you.
Select f.Id, (Select Family_Member__c, Family__c, Family_Role__c From Family_Members__r) From Family__c f where id in (select Family__c from Family_Member__c where Family_Member__c in :setoffamilymembers)