+ Start a Discussion
B2000B2000 

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)

Best Answer chosen by Admin (Salesforce Developers) 
ClintLeeClintLee

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

Starz26Starz26

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

  }

 

}

B2000B2000

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!

ClintLeeClintLee

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 ) ];

 

 


B2000B2000

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"

 

ClintLeeClintLee

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 );

      }

}

           

This was selected as the best answer
B2000B2000

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)