I'm stuck on this simple dilemma that I was hoping to get answered. I have 3 objects:
There is a Parent-Child relationship from Quote (Parent) to Quote_Year__c (Child) AND a Parent-Child relationship from Quote (Parent) to QuoteLineItem (Child).
I'm essentially trying to relate my QuoteLineItem records to my Quote_Year__c records. I know how to query the data so I can get the joined data properly. This query:
SELECT Id, (SELECT Id FROM QuoteLineItems), (SELECT Id FROM Quote_Years__r) FROM Quote WHERE Id IN (SELECT QuoteId FROM QuoteLineItem) AND Id IN (SELECT Related_Quote__c FROM Quote_Year__c)
My dilemma is that I basically want an outer join on the QuoteLineItem records. So instead of having "72 total records" (i.e. the record count of my parent object), I'm wanting to have closer to 11,000 (the record count of my QuoteLineItem object). But... as far as I understand, I can't query on the QuoteLineItem object to get all the data I want since it, like Quote_Year__c, is a child to the Quote object. Thus, I can't use relationships to access the Quote_Year__c object from QuoteLineItem. If I'm wrong about that, please let me know, too.
Ok, so I have 2 possible paths I could take and I'm looking to get help on finding a solution to either one (no preference). This will all be done in an Apex class as well:
I can use 2 different queries. I can query on the QuoteLineItem records and on the Quote_Year__c records separately (since both are children to the Quote object), then do an outer join on them, on the Quote Id, in my Apex class. There are a lot of records and this will be a high-usage class so is there an efficient way of doing an outer join on 2 SOQL queries without iteration mapping?
I can manipulate the the query in a way (either in workbench, my Apex class, etc...) that allows me to perform an outer join on the Quote Id in one query instead of joining 2 separate ones. If that is possible, how could I go about doing that?
If anyone is feeling generous enough to offer solutions to both scenarios, I won't complain. :)