+ Start a Discussion

Simple Query Question How To....

I know we are not allowed to have a subquery on the same object as our main query, so I am not sure how to write the following SOQL:


Select  payout__Households__c
from Account
where payout__Households__c  in


  Select  payout__Households__c
   from Account
   where id = '001A000000Iw0nxIAB'



Thanks in advance!



What kind of a field is payout_Holdholds__c?  Is it a multi-valued picklist or a comma-separated list of values? 


Someone else may be able to give you an answer for a single SOQL statement, but if I were solving it, I would use two statements.  The first would be your inner query.  The second would use the values from with with via INCLUDES.  In between the two statements, I would unpack the values in payout_Households__c, something like this (error handling would need to be added):


Account acct = [Select  payout__Households__c from Account where id = '001A000000Iw0nxIAB']);

String payouts = '\'' + acct.payout__Households__c.replace(';','\',\'') + '\'';

List<Account> payoutAccts = [SELECT payout_Households__c FROM Account

                                                WHERE payout_Households__c INCLUDES :payouts];


Of course, that all depends on how the data is stored in the payout_Households__c field.  This is not cut-and-pastable code above, but just to give you a general idea.  I've not actually used an INCLUDES in a SOQL statement directly as above, but have used it just like this when building a dynamic SOQL statement that is executed by Database.query.


payout__Holdings__c is an Id field....




I just want to do a sub query against the SAME object (Account) as the main query. I know there is the PARENT.  (dot) syntax that is supposed to work but I haven't had any luck with that.


Oh.  An id field.  That's a different answer altogether!


You should be able to just:

   SELECT payout__Holdings__c from Account WHERE payout_Holdings__r.id = '001A000000Iw0nxIAB'


I can do that on the Account object where I have a custom id field pointing to another Account record.






The question is why do you need this,I mean what is the use case behind it.Then,I can suggest you something.