6

I am trying to get all leadless payments that are attached to a subscription given that subscription does not have a null lead, but if you see my query below, it only achieves half of the goal.

It gets all leadless payments that are attached to a subscription but I also need to ensure it retrieves records for those subscriptions who also do not have a null lead. How would I do such a subquery?

SELECT Id, Subscription__c FROM Payment__c
WHERE Lead__c = NULL AND Subscription__c != NULL
  • 1
    can u add details on how all these 3 objects (Payment, Lead and Subscription) are related to each other ? – Vamsi Krishna Aug 5 '16 at 11:39
  • 1
    @VamsiKrishna Lead can have subscriptions and payments. A payment can have a lead and a subscription. A subscription can have a payment and a lead. – Ali Gajani Aug 5 '16 at 12:16
4

In soql you can reference grandparent as follows:

AND Subscription__r.Lead__c != null

You might just want to create a checkbox formula and query on that instead.

AND(
    ISNULL(Lead__c),
    NOT(ISNULL(Subscription__r.Lead__c))
)
| improve this answer | |
  • 1
    Excellent answer – Ali Gajani Aug 5 '16 at 12:52
  • @AliGajani Was my interpretation correct? I think your OP left some people guessing. – Adrian Larson Aug 5 '16 at 13:01
  • Yes. I clarified further below Vamsi's question on my OP. :) – Ali Gajani Aug 5 '16 at 13:41
  • @AliGajani If this answer addressed your problem, please consider accepting it by clicking on the check mark/tick to the left of the answer, turning it green. This marks the question as resolved to your satisfaction, and awards reputation both to you and the person who answered. If you have >= 15 reputation points, you may also upvote the answer if you wish. There is no obligation to do either. – Adrian Larson Aug 5 '16 at 13:43
  • I did, thanks. By the way, how do I retrieve the Email on a Lead object in this very query. I would love to have that too. Thanks a lot. – Ali Gajani Aug 5 '16 at 15:13
1

It sounds to me like you don't need a subquery, but instead need an OrderBy clause based on the value of Lead__c with something like Nulls first. What I hear you saying is that you want data returned for both when Lead__c is null and when it isn't.

SELECT Id, Subscription__c, Lead__c FROM Payment__c WHERE Subscription__c != NULL 
    ORDER BY Lead__c ASC NULLS FIRST

Now you can sort your results and split the list where the nulls end if you need to.

| improve this answer | |
  • I think it's parent Lead is null but grandparent Lead (via Subscription) is not. Not 100% sure but that's my interpretation. – Adrian Larson Aug 5 '16 at 11:57
  • 2
    That part wasn't clear in his question. He didn't answer @VamsiKrishna question to add that clarification. In either case, he's still not doing a subquery. – crmprogdev Aug 5 '16 at 11:59
  • The only way to access a lead is via subscription, because leadless payments dont have leads. This query won't work as its querying for lead existence on payment, something which is already implied. – Ali Gajani Aug 5 '16 at 12:51
  • Then you may still want to add the OrderBy clause to @AdrianLarson answer to help you separate the two types of payments. – crmprogdev Aug 5 '16 at 13:01
0

SELECT Id, Subscription__c FROM Payment__c WHERE Lead__c = NULL OR Subscription__c != NULL

| improve this answer | |
  • 1
    Please add explanation and not just a code dump. – Adrian Larson Aug 5 '16 at 11:48
  • Can you please tell me your object relationship? means how they are related to eachother. – Amol Jadhav Aug 5 '16 at 12:45

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.