SOQL Statment - Child - Parent - Child [ Edited ]

[TylerM]

Can anyone help change this SOQL statment so that I get the results that I am expecting?

I have three objects:

1. OppPSQLineItem__c (Relationship Name = Part_and_Service_Quotes__r) (Child)

2. Part_and_Service_Quote__c (Parent)

3. Part_and_Service_Quote_Line_Item__c (Child)

I am trying to filter based on an ID (Cost_Sheet__c) from the object OppPSQLineItem__c and show all the related records in the objects sibling (Part_and_Service_Quote_Line_Item__c). The statement will bring back ALL records contained in Part_and_Service_Quote_Line_Item__c as it is a right outer join. Any way to change to a left join? Am I coming at this from the wrong direction?

SELECT Id, (SELECT Id, Cost_Sheet__c FROM Part_and_Service_Quotes__r WHERE Cost_Sheet__c = 'a0R60000000aIhY'), (SELECT Id FROM Part_and_Service_Quote_Line_Items__r) FROM Part_and_Service_Quote__c

I really appreciate any help and effort anyone in the community puts forth!

Regards,

Tyler

[Starz26]

I may not beunderstanding correctly but you may have to get a set of IDs you want to return. I am assuming Cost_Sheet__c is common between the two children?

Set<ID> csIDs = New Set<ID>();

For(OppPSQLineItem__c o : [SELECT Id, Cost_Sheet__c FROM Part_and_Service_Quotes__r WHERE Cost_Sheet__c = 'a0R60000000aIhY']){

      csIDs.add(o.id);

then

SELECT Id,  (SELECT Id FROM Part_and_Service_Quote_Line_Items__r Where Cost_Sheet__c IN :csIDs) FROM Part_and_Service_Quote__c

[TylerM]

Hi Starz26,

I really appreciate the response! Unfortunately, Cost_Sheet__c is actually not common between the two children (this would make your solution perfect). Cost_Sheet__c is actually a Parent of OppPSQLineItem__c. OppPSQLineItem__c has actually been created to model a many-to-many relationship between Cost_Sheet__c and Part_and_Service_Quote_Line_Item__c.

Does this give enough information to help with a different solution? Unfortunately I am still banging my head against a wall!

Tyler

[TylerM]

Seems like I was too hasty heading to the community to find the answer. It appears the following statement works:

SELECT Name, (SELECT Id, Name, Labour_Hours__c, Make__c, Model__c, Price__c, Quantity__c, Line_Description__c, Subtotal__c FROM Part_and_Service_Quote_Line_Items__r) FROM Part_and_Service_Quote__c WHERE Id IN (SELECT Part_and_Service_Quote__c FROM OppPSQLineItem__c WHERE Cost_Sheet__c = 'a0R60000000aIhY')

Thanks for the help!

[Starz26]

I was just going to send something similiar....glad you got it working.