Apex Code Development You need to sign in to do that
Don't have an account?
Masie SOQL query is not returning all children records
May someone please help.
If we run this query :
SELECT Name ,Id , Mercantile_Merchant_Number__c , Date_Installed__c , PriceBook__c ,
(SELECT Ad_Hoc_Billing_Item__c.Effective_Date__c, Ad_Hoc_Billing_Item__c.Product__r.ProductCode,
Ad_Hoc_Billing_Item__c.Quantity__c
from Ad_Hoc_Billing_Items__r) from Account
where Mercantile_Merchant_Number__c <> null
Then we get two results that does not include a specific record but if we include the specific record's id in the query like this:
SELECT Name ,Id , Mercantile_Merchant_Number__c , Date_Installed__c , PriceBook__c ,
(SELECT Ad_Hoc_Billing_Item__c.Effective_Date__c, Ad_Hoc_Billing_Item__c.Product__r.ProductCode,
Ad_Hoc_Billing_Item__c.Quantity__c
from Ad_Hoc_Billing_Items__r) from Account
where Mercantile_Merchant_Number__c = '360000000041032'
we get a result. The Ad Hoc Billing Item is a child of the account table. Is it in the way we are nesting the statements?
If we run this query :
SELECT Name ,Id , Mercantile_Merchant_Number__c , Date_Installed__c , PriceBook__c ,
(SELECT Ad_Hoc_Billing_Item__c.Effective_Date__c, Ad_Hoc_Billing_Item__c.Product__r.ProductCode,
Ad_Hoc_Billing_Item__c.Quantity__c
from Ad_Hoc_Billing_Items__r) from Account
where Mercantile_Merchant_Number__c <> null
Then we get two results that does not include a specific record but if we include the specific record's id in the query like this:
SELECT Name ,Id , Mercantile_Merchant_Number__c , Date_Installed__c , PriceBook__c ,
(SELECT Ad_Hoc_Billing_Item__c.Effective_Date__c, Ad_Hoc_Billing_Item__c.Product__r.ProductCode,
Ad_Hoc_Billing_Item__c.Quantity__c
from Ad_Hoc_Billing_Items__r) from Account
where Mercantile_Merchant_Number__c = '360000000041032'
we get a result. The Ad Hoc Billing Item is a child of the account table. Is it in the way we are nesting the statements?
Are you getting 2 records of Accounts? Also how are you checking the records? Are you doing it via developer console or printing the results in a apex code and then checking the debug logs. If you are checking in developer console, then it currently does not show child records of sub query properly. You can try using apex code and the printing the records in debug log.
Please Mark this as Answer if it helps you
--
Regards,
Grazitti Team
Web: www.grazitti.com
I am getting a result that there are only two accounts with the child records which is incorrect. I was doing this in the developer console as well as force.com explorer with the same result.
Do you mean you want to pick only those records which have a child record? Is it that Account with Mercantile_Merchant_Number__c = '360000000041032' is not showing in the first query?
I tried a simple query SELECT id, name,accountnumber , (select id from contacts) from account where accountnumber <> null in my developer console and it returns all accounts irrespective of any child contact and excludes only those accounts for which accountnumber is null. Is it working differently for you?
Sorry looks like I am still not clear with question.
--
Regards,
Grazitti Team
Web: www.grazitti.com
The problem is that when I run a query saying return all records regardless of whether they have a child record or not except those account records where the Mercantile_Merchant_Number__c is not blank then I get only two accounts with related child records and none of these is the account with Mercantile_Merchant_Number__c = '360000000041032' and yet if I add the specific Mercantile_Merchant_Number__c in the query I get a result showing they have these child records.
In total there are probably about 100 of my accounts that have these child records and yet it is only bringing back two in the query.
I hope this is clear enough to assist in this problem.
Hi,
Got it now. Can you try and use Mercantile_Merchant_Number__c <> '' in the where clause. May be that will work. In the meantime I will try and reproduce in my Org.
Please Mark this as Answer if it helps you
--
Regards,
Grazitti Team
Web: www.grazitti.com
I tried you suggestion and I am getting the same result. Thanks or should I say grazie for your help, I will await your reply.