How to solve when an error is found while running SOQL help with subquery where clause?

182 Asked by DianaCampbell in Salesforce , Asked on Aug 24, 2023

I have made two tables, service order and status history (child table). I want to find all the service orders which have a status history record matching ‘Order Complete.’ I have used some queries. But I am getting the error ‘unknown error parsing query.’

SELECT Id, Name, CreatedDate, (SELECT Name FROM Status_History__r ORDER BY CreatedDate DESC LIMIT 1)
FROM Service_Order__c
WHERE id = (SELECT id FROM Status_History__r WHERE Name = 'Order Completed')

If I use a new query using SOQL, I am also getting an error that shows that ‘Order Error at Row:3:Column:29 subject type ‘Status_History_r’ is not supported. It also says that I must append the ‘_c’ to use a custom object and asks me to reference the WSDL for an accurate name.

Answered by David Edmunds

  SELECT Id, Name, CreatedDate, (SELECT Name FROM Status_History__r ORDER BY CreatedDate DESC LIMIT 1) FROM Service_Order__c WHERE id IN (SELECT Service_Order__c FROM Status_History__c WHERE Name = 'Order Completed')

You can use the above-mentioned code if you need. The child relationship or Status_Histories_r will be found if you navigate to UI to custom objects. You will find an object Status History. Now, see the lookup field, which has Service Order.

Also, you can use the following query as an SOQL subquery:

How to solve when an error is found while running SOQL help with subquery where clause?

Your Answer