Add fields in relationship queries

Question

Nested Queries:

List<Order> orders = [SELECT Id, Status, Pricebook2Id, Order_Qty_Multiplier__c, (SELECT Id,Pricebook__c FROM Order_Pricebooks__r) FROM Order WHERE Id IN (SELECT OrderId FROM OrderItem WHERE Id IN :Trigger.newMap.keySet())]

The above Queries is right

I want to query another two Fields from Orderitems such as Product_Group__c,OrderType__c

So I rearrnge the Query like this:

List<Order> orders = [SELECT Id, Status, Pricebook2Id, Order_Qty_Multiplier__c, (SELECT Id,Pricebook__c FROM Order_Pricebooks__r) FROM Order WHERE Id IN (SELECT OrderId,Product_Group__c,OrderType__c FROM OrderItem WHERE Id IN :Trigger.newMap.keySet())]

error Unexpected Token:

How to Rectify the issue and How to write a Nested Query for above Condition Share the Answer

Answer 1

You need to add a sub query in the select portion of your query like

List<Order> orders = [SELECT Id, Status, Pricebook2Id, Order_Qty_Multiplier__c, (SELECT Id,Pricebook__c FROM Order_Pricebooks__r), (SELECT OrderId,Product_Group__c,OrderType__c FROM OrderItems) FROM Order WHERE Id IN (SELECT OrderId FROM OrderItem WHERE Id IN :Trigger.newMap.keySet())]

*OrderItems is the relatioship name to be used when included in subquery

The subquery in where condition of the original query can only return an id of the original object being queried. So only you got error when you add fields to it.

Hope it helps.