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.

-1

You need 2 Queries because you cannot query using 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/

  • 1
    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 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 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. – disasterlooms Mar 23 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 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.

  • 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 at 18:42
  • Then it is not possible and you need two separate queries, one for each clause. – Adrian Larson Mar 22 at 18:43

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.