+ Start a Discussion

VF Page page - How to query records which does not have any records in a related object.

We have 2 custom objects


Order Line
Inventory Allocations


Inventory Allocation has a lookup relationship to Order Line


When Order Line is created then it may create inventory allocation records when inventory is available.


So there can be situations where a order line has no inventory allocations.


How can I query the order lines which does not have any inventory allocations.


I need to create a VF page which shows these records and process the records to create new inventory allocations.

 I am looking for ability to do something similar to this
SELECT o.Id, o.nAME FROM OrderLine__c o
where (select count() from gii__InventoryAllocation__c where OrderLine__c = o.id) = 0
So that I can get a list of Order Lines and I can display them in a VF page


In order to achieve your objective you can check the size the of child records, if there is lookup relationship between Order Line & Inventory Allocations.

Refer to the following code for clarity:-

list<OrderLine__c> orderln = [SELECT o.Id, o.nAME, (select id from InventoryAllocation__r)  FROM OrderLine__c o limit 1]; system.debug( ' Child Records -->'  +  orderln[0].InventoryAllocation__r.size());



orderln[0].id=OrderLine__c id which don't have any related records.Now put your desire code here.



Did this answer your question? If not, let me know what didn't work, or if so, please mark it solved. 



Please try following :-


[select id, name from OrderLine__c where ID NOT IN (select OrderLine__c from gii__InventoryAllocation__c)]


Please make sure about the soql limits before implementing it in real application.