function readOnly(count){ }
Sign Up ›
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
Learn More

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
TylerM.ax1133TylerM.ax1133 

SOQL Statment - Child - Parent - Child

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

Starz26Starz26

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

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

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!
Starz26Starz26

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