0

I have a many to many relationship with 3 objects Position, Candidate, Job Application(Junction Object). I wanted to retrieve all the positions applied by a particular candidate and also the positions not applied by any candidate using one query. Is it possible using semi joins and anti joins? Or I have to create separate lists and then combine them into third list.

2 Answers 2

0

You need 2 Queries because you cannot query using the "OR" clause in a subquery. In SOQL it is either include specific child records or exclude child records but not both. I have used sub queries in the select clause and then used when in other records as a subquery but it would not work in this situation because you want to exclude records that you need.

I do want to share this blog. I think it gives a great explanation of what can be done with SOQL.

https://blog.jeffdouglas.com/2010/02/22/soql-how-i-query-with-thee-let-me-count-the-ways/

4
  • 2
    A real answer would include at least some pertinent information. We do not generally condone link-only answers, since the knowledge would be lost if the link were deleted. Please edit your answer and include the relevant information.
    – sfdcfox
    Mar 22, 2019 at 18:31
  • I don't have a schema as such. The original objects are the different ones. Position__c ---<---- Job_Application__c --->--- Candidate__c
    – Saloni
    Mar 22, 2019 at 18:53
  • I edited my previous answer. There are ways to get around the no 'or' clause in subqueries but it won't work in your use case. Soql won't let you use exclude and include filter results when using a subquery. Mar 23, 2019 at 22:13
  • Thats what I thought to create 2 queries and combine the result in one. But thanks for the explanation. Really appreciate it.
    – Saloni
    Mar 25, 2019 at 12:55
0

Yes it should be possible. I believe you are limited to two semi- or anti-joins in a single query, but you only need two. Making some assumptions about API Names, a query looking something like the below should work:

SELECT Id FROM Position__c WHERE Id IN (
    SELECT Position__c FROM Job_Application__c
    WHERE Candidate__c = 'xyz'
) OR Id NOT IN (
    SELECT Position__c FROM Job_Application__c
)

You might also be limited to one join on a specific table in one query, I'm not sure but will investigate and update this answer.

3
  • Thanks Adrian. I also tried to do this, but with Salesforce there is a limitation that we cannot use subqueries with OR.
    – Saloni
    Mar 22, 2019 at 18:42
  • Then it is not possible and you need two separate queries, one for each clause.
    – Adrian Larson
    Mar 22, 2019 at 18:43
  • @AdrianLarson I suggest you update this answer given the OR issue raised by Saloni (and just hit by me in my package development work!)
    – Phil W
    Jun 16, 2020 at 15:24

You must log in to answer this question.

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