You need to sign in to do that
Don't have an account?
Andy Kallio 7
Admin struggling with a soql
HI All. The query is on a junction object called Project_Contact_Role__c which links the Project__c object to the Contact object. The org has another junction object called datapm__OptinmOptin__c, which links the same two objects (Project__c and Contact). What I want the query to do is find Project_Contact_Role__c records that do not have a corresponding record in the other junction table.
So, I've written this query, but it doesn't work quite right. It works if there are no records in datapm__OptinmOptin__c with either same contact id or project id, but if there is a datapm__OptinmOptin__c with same project id and a different contact id or vice versa then it fails.
The only thing I can think of doing is adding a lookup from datapm__OptinmOptin__c to Project_Contact_Role__c. And then I can change the query to this:
Just want to see if there is anyone that can see a way to fix the first query. Thanks!!
So, I've written this query, but it doesn't work quite right. It works if there are no records in datapm__OptinmOptin__c with either same contact id or project id, but if there is a datapm__OptinmOptin__c with same project id and a different contact id or vice versa then it fails.
select Id, Project__c, Contact__c from Project_Contact_Role__c where Id IN :pcrs and Contact__c NOT IN (select datapm__Contact__c from datapm__OptinmOptin__c) and Project__c NOT IN (select Project__c from datapm__OptinmOptin__c)
The only thing I can think of doing is adding a lookup from datapm__OptinmOptin__c to Project_Contact_Role__c. And then I can change the query to this:
select Id, Project__c, Contact__c from Project_Contact_Role__c where Id IN :pcrs and Id NOT IN (select Project_Contact_Role__c from datapm__OptinmOptin__c)
Just want to see if there is anyone that can see a way to fix the first query. Thanks!!
All Answers
Thanks for the help Pradeep. Your code wasn't quite right, but it got me there. Here is what it eventually turned into.