Apex/SOQL - Parent - Child - Grandchild join and insert into another object

Question

My background is SQL & am finding challenging while handling some scenarios in SOQL. Here is my scenario:

We have 3 objects that are related (Parent_Obj -> Child_Obj -> Grand_Child_Obj).

We build Master-Detail relationship across these objects & captured data. Now, my job is to populate a new object Combined_Obj using Apex/SOQL which contains information from all the 3 related objects.

To me this is a straight forward activity in SQL (something like this):

INSERT INTO Combined_Obj (col1, col2,...)

SELECT Parent_Obj.Name, Parent_Obj.CourseName, Child_Obj.StudentName, Grand_Child_Obj.Marks
FROM Parent_Obj, Child_Obj, Grand_Child_Obj
WHERE Parent_Obj.ID = Child_Obj.Parent_Ref_ID
AND Child_Obj.ID = Grand_Child_Obj.Parent_Ref_ID;

Is something like this possible in SOQL?

I did a SOQL between 3 objects:

SELECT Child_Obj__c.ID, Child_Obj__c.Name, Parent_Ref__r.Name, 
(SELECT Name from GrandChild_Objs__r) from Child_Obj__c

But this is now need to be inserted back to into the Combined_Obj.

Is it possible to make SOQL to directly insert the selected data into Combined_Obj without processing through Apex loops?

Answer 1

On the one hand, you can navigate through relationships in SOQL using right inner and outer joins such as this:

SELECT Id, Child__r.Id, Child__r.Parent__r.Id FROM Grandchild__c

You can also use left joins to filter or bring the related child list of records, given that you're querying the parent table. But you can't exactly query from 3 tables in the same query. In that case you would have to make three or two separate queries depending on your requirement.

I'm not sure your exact requirement, but that should help you. Here is a resource with many SOQL advanced features.

Regarding your other question, SOQL can't insert records just bring them from DB. You would have to, as you commented, to loop over the records to make any modifications you want and then insert/update the list.