0

I am trying the below SOQL, for some reason it doesn't work. I tried the SOQL inside where clause alone that works,

select Id,Name from Offer__c where Id in (select Offer_Id__c from Segment_Offers__c where Segment_Id__c ='a2n17000000EhO7AAK' order by Offer_Priority__c ASC LIMIT 4)
1
  • What do you mean by it doesn't work ? Do you get an error or just no records ? Feb 7, 2021 at 10:37

1 Answer 1

2

SOQL is not the same as SQL and does not support much in the way of expressions in the right-hand side of a "fieldExpression". It does, as Derek F points out in the comments, have some semi-join and anti-join capability using "IN (SELECT ...)" or "NOT IN (SELECT ...). However, this only works in specific scenarios.

Some options you have here are:

  1. To execute the queries separately and generate a binding variable to substitute the Id match options.
  2. To change the query to select the data a different way.
  3. To ensure you align with supported SOQL semi-join capabilities

In this case, applying option 2, perhaps you could use:

SELECT Offer_Id__c, Offer_Id__r.Name
    FROM Segment_Offers__c
    WHERE Segment_Id__c = :id
    ORDER BY Offer_Priority__c ASC LIMIT 4

(Always best to use a binding for a value like an ID and to obtain that dynamically rather than hard-coding the value, which can change from org to org.)

In this case you simply need to fetch the name values through the returned Offer_Id__r.Name and either Offer_Id__c or Offer_Id__r.Id values.

4
  • The WHERE <id field> IN (<subquery>) pattern is supported by SOQL. It's called a semi-join (or anti-join if you use NOT IN). It's covered in the second section of SOQL comparison operators
    – Derek F
    Feb 7, 2021 at 13:22
  • This is only for directly related parent/child though, right? Here the relationship is not being used in an appropriate way from what I can see.
    – Phil W
    Feb 7, 2021 at 13:37
  • BTW, happy to delete my answer if you have a better one.
    – Phil W
    Feb 7, 2021 at 13:39
  • Thanks @Phil W your query works. but still have no idea why mine didn't work. I have seen that usage everywhere on the internet. It says error parsing query. But for the time being I am happy and can continue with my work. Thanks again.
    – sumchans
    Feb 7, 2021 at 17:18

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .